Attach workbook then send it

Question

When I run the code below it will take the workbook that is currently open and attach it to an email.  I would like it to also send the email to a specific address like ******@aol.com but I am not sure how to do that.  Do you know how?

Application.Dialogs(xlDialogSendMail).Show

Answer 1

Like this:

Application.Dialogs(xlDialogSendMail).Show Arg1:="myemailaddress"

where myemailaddress is the e-mail address of the recipient. You can also specify the subject:

Application.Dialogs(xlDialogSendMail).Show Arg1:="myemailaddress", Arg2:="Monthly report"

Answer 2

The dialog creates an e-mail but doesn't send it. You could use

ActiveWorkbook.SendMail "myemailaddress", "Monthly Report"

but you'll still run into Outlook security if Outlook is your default e-mail program, and have to allow the message to be sent.

Answer 3

The particular function does not allow for the message to be automatically sent - merely displayed. If you want to send it, via Outlook, then try the following instead. Outlook must be configured to send mail immediately or it goes to the Outbox.

Sub SendMyWorkBook()

Dim olApp As Object

Dim olMail As Object

Dim strFileName As String

Dim bStarted As Boolean

ActiveWorkbook.Save

strFileName = ActiveWorkbook.FullName

Set olApp = GetObject(, "Outlook.Application")

If Err <> 0 Then

    Set olApp = CreateObject("Outlook.Application")

    bStarted = True

End If

Set olMail = olApp.CreateItem(0)

With olMail

   .To = "someone AT somewhere.com" 'email address

    '.CC = "someoneelse AT somewhere.com"

    '.BCC = "someoneelse AT somewhere.com"

    .Subject = "Monthly Report" 'Message subject

    .Body = "Report updated " & Format(Date, "d MMMM yyyy") 'message body

    .Attachments.Add strFileName

    .Send

End With

If bStarted Then

    olApp.Quit

End If

'Clean up

Set olMail = Nothing

Set olApp = Nothing

End Sub

Answer 4

Thanks for the help.  This is great.  For some reason the message still is not sending.  Any thoughts?