Can you automate e-mail within macro?

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!
 
R

Ron de Bruin

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
 
M

Michelle72

This is a question for Ron:
Is it possible to do this with Outlook Express? What changes need to be
made?

Thank you
 
M

Michelle72

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
 
Top