VB Excel Macro

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
       Workbooks(wbName).Activate
       aName = Range("A4:G4").Select
       Sheets(Worksheets(w).Name).Select
       Sheets(Worksheets(w).Name).Copy
       'If it is the main page
       If IsEmpty(aName) Then
       'Do Nothing
       Else
        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"
           .Send
        End With
    End If
    Set objMail = Nothing
   Next w
   
   Set objOutlook = Nothing

End Sub

Thank you for your time.... :-)