excel-training-advanced courses - macro automate emailing spreadsh

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel-training-advanced courses - Macro to automate e-mailing of spreadsheet | Excel forum

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

resolvedResolved · Low Priority · Version Standard

Kevin has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Macro to automate e-mailing of spreadsheet

I want to add a button within a spreadsheet which will open outlook and attach the completed spreadsheet. how do I do that?

RE: 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


 

Excel tip:

Navigate with keyboard without losing your active cell

If you like using your keyboard to scroll through your excel document, but want to keep your active cell the same, use the scroll lock, and then use your arrow keys to navigate around the document.

View all Excel hints and tips


Server loaded in 0.08 secs.