Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

572 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