Can you automate e-mail within macro?

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Please help! I would like to use a macro to e-mail say 20
different files to 20 different individuals. The files
would change but the names would remain the same. And,
each individual file would go to the same recipient each
time.

I know you can EM from within Excel, but when I tried to
incorporate that function into a macro, it would only
take me to the point of entering the recipients name. I
would like to be able to fill in the recipient
automatically within the macro from an address book or
email group.

Can this be done? If so, how? Thanks for any guidance
you can give me!
 
Hi Brian

If you have the information in your worksheet and use Outlook
Try this example sub

Don't forget to set a reference to Outlook in the VBE editor.

In column A you must place the names of the people
In column B the Adresses
In column C the Filenames like this C:\Data\Book2.xls


Sub Outlook_Mail_every_Worksheet()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
.Attachments.Add cell.Offset(0, 1).Value
.Display
End With
Set olMail = Nothing
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
 
This is a question for Ron:
Is it possible to do this with Outlook Express? What changes need to be
made?

Thank you
 
Thanks for your response Ron! I broke down and bought Outlook and I've
now got everything set up just like I wanted with one exception...
The macro creates several ready to be sent emails for me to the proper
addresses with the proper attachments but then I have to go and click
on "Send" about 100 times to get them all to go. Is there something I
could add to the existing macro or anything I could add to automate the
"Send" part of the equation? Any help is greatly appreciated...

Thank you,
Michelle
 
Back
Top