VB Excel Macro

Posted on 2003-03-14
Medium Priority
Last Modified: 2010-05-01
To give a brief overview I need a macro that will take a spreadsheet that has numerous worksheets in it and separate them, save them in a location and then e-mail them out by looking for the e-mail address in outlook. I have been working with the code that was provided to me and below is what I have come up with. I am having 3 problems with it though.

1.) The first tab in my worksheet is the master sheet and should not be e-mailed or saved as a new worksheet. There is nothing in the range from cell A4 to G4 on this sheet but there is on the rest of them. So I was trying to use an if statement to check for this condition. However when I run it for the first sheet it does hit the else and runs though all of the code even though the range is empty.

2.) The line: .Attachments.Add strAttachMent, , , "Occupancy Report"
is causing an error stating that The system can not find the path that is specified. Should I be using a variable name for where I have "Occupancy Report"? How can I get the attachment to work?

3.)Every time I run this when it is looking in the address book I get a message that says: A program is trying to access e-mail addresses you have stored in outlook. Do you want to allow this?  Is there anyway to turn this message off so that I do not have to click yes 210 times?

Also is there a way to specify what inbox in outlook that it will use? I have my personal e-mail account and an account that will have all of the address book records needed for this report, or will it just use which ever one that I have open at the time?

Here is what I have:

Sub Macro1()
   Dim objOutlook As Outlook.Application
   Dim objMail As MailItem
   Dim olRecipient As Recipient
   Dim aName As String
   Dim aCopyTo As String
   Dim fName As String
   Dim wbName As String
   Set objOutlook = New Outlook.Application
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   wbName = ActiveWorkbook.Name
   For w = 1 To Worksheets.Count
       aName = Range("A4:G4").Select
       'If it is the main page
       If IsEmpty(aName) Then
       'Do Nothing
        fName = "C:\Documents and Settings\Tatha\Desktop\" & ActiveCell.Value & ".xls"
        ActiveWorkbook.SaveAs Filename:=fName
        Set objMail = objOutlook.CreateItem(olMailItem) 'Create a new mailitem
        With objMail
            Set olRecipient = objMail.Recipients.Add(aName)

           .Subject = "Occupancy Report"
           .Body = "Here is the current month's occupancy report. Please complete the form and return it us using the options listed on the report."
           .Attachments.Add strAttachMent, , , "Occupancy Report"
        End With
    End If
    Set objMail = Nothing
   Next w
   Set objOutlook = Nothing

End Sub

Thank you for your time.... :-)
Question by:Needs
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8139400
In response to #3

Although you did not say so, I assume you are using Outlook XP or
Outlook 2000 with the most recent service pack. The bad news is that
unless you are using Exchange Server you cannot turn off this
functionality. Exchange Server users can set whether these options are
on or not but us small offices cannot. I am uninstalling Outlook XP and
reinstalling 2000 for just this reason.

Accepted Solution

dmang earned 300 total points
ID: 8154258
Hi Needs...
Try this..
1.  to avoid a particular worksheet...

   For w = 1 To Worksheets.Count
      if sheets(w).name <> "MasterSheetName" then
         aName = Range("A4:G4").Select

2. Use the variable Fname in place of strAttachMent (my mistake originally)
3.  the warning prompt is painful....I found this awhile back for the same reason.. Check this page for the "ClickYes" program


4.  You can try specifying the address book ..

Set myAddressList = objoutlook.AddressLists("Personal Address Book")

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question