automated e-mails

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

Brian

Hi! Is it possible to totally automate an outgoing e-mail
with a file attachment through a macro. I have been able
to get the e-mail screen in outlook with my file
attached. But, I want the macro to be able to completely
do an e-mail. That is, type in a pre-determined message
and actually send the message with the attachment.

What I want Excel to do is similar to what Access can do
through a macro. In Access you can have a macro open an
object, pick a recipient, attach the object, and actually
send the message.

Any help would be most appreciated as this is the final
piece to a huge project I have been working on.

Thanks a million!
 
Here's a modified version of a function I use...

'Test the bMailReport function below by attaching the active workbook
Sub TestEmail()
If Not bMailReport(ActiveWorkbook) Then MsgBox "Test Email Failed!",
vbCritical, "Test Email"
End Sub

'wkbReport = File we're attaching
Function bMailReport(wkbReport As Workbook) As Boolean

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim szFile As String

On Error GoTo Error

'Assume success
bMailReport = True

'Attaches the workbook passed into this function using the variable
wkbReport
szFile = wkbReport.FullName

'''Create the Outlook object
Set objOutlook = CreateObject("Outlook.Application")

'''Access and display a new mail item
Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
'Send the message in plain text format
.BodyFormat = olFormatPlain
'Addresses the message to an email and a name in my contact list
.Recipients.Add "(e-mail address removed); Jeanne Cotton"
'Activates Outlook's "Check Names" feature and looks for Sam Smith
in your contact/address lists
.Recipients.ResolveAll
.Subject = "Test Email"
.Body = "Attached please find the Report for today." & vbCrLf &
vbCrLf & "If you have any questions please call."
.Attachments.Add szFile
.ReadReceiptRequested = True
.Importance = 2
.Display
'.Send 'Use this line instead of ".Display" if you want to send it
directly
End With

'Clear the Outlook session/variables from memory
Set objMailItem = Nothing
Set objOutlook = Nothing

Exit Function
Error:
bMailReport = False
End Function
 
Back
Top