VB Excel Macro
Posted on 2003-03-14
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:
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
fName = "C:\Documents and Settings\Tatha\Desktop\" & ActiveCell.Value & ".xls"
Set objMail = objOutlook.CreateItem(olMailItem) 'Create a new mailitem
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"
Set objMail = Nothing
Set objOutlook = Nothing
Thank you for your time.... :-)