excel-training-advanced courses - Macro to automate e-mailing of spreadsheet

Hi Kevin

Thankyou for your question

If you create you button on the tool bar and set it to run the following code, you should find it works OK, although we my need to tweak it further to meet your specific requirements.

In particular the recipient and subject are hard coded and you might want to repalce them with variables so the user can define them at run time

Public Sub sendemail()



Dim Outlook As Object

Dim NameSpace As Object

Dim MailItem As Object

Dim recipient As String



recipient = "bob@acompany.com"



Set Outlook = CreateObject("Outlook.Application")

Set MailItem = Outlook.Createitem(0)



With MailItem

    .Subject = " Test Email"

    .Recipients.Add recipient

    .body = "Workbook"

    .send

 End With

 

End Sub