Export & Email

[Tangles]

Hi everyone,

Before I start, I just want to make it known that I'm not necessarily new to programming but I'm relatively new to VisualBasic, so bear with me!

I'm trying to create a macro that will export the current sheet as a PDF file, and send it as an attachment on an email to predetermined recipients with a predetermined subject.

I've tried using the "ActiveWorkbook.SendMail" function but it doesn't export as a PDF, and I can't seem to get the string right for the Recipients object to send to multiple recipients.

I'd like the least amount of user input as possible (ie I click the button, and it exports and sends the email without input).

Any help is greatly appreciated.

 

[davesexcel]

Here is something you can work with to get started.

Ths saves the pdf to your temp folder.
Then attaches the file to your email.

Sub EmailOnePDFSheet()
    Dim oApp As Object
    Dim oMail As Object
    Dim wb As Workbook
    fName = Environ("Temp") & "\copySheet.pdf"

    'Turn off screen updating
    Application.ScreenUpdating = False
    'PDF sheet and save it to a temporary file
    Sheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName

    'Create and show the outlook mail item
    Set oMail = CreateObject("Outlook.Application").CreateItem(0)
    With oMail    'Add a recipient
        .To = "Marcus.Small@YourEmail.com.au"
        .Subject = "One Page Activesheet v2"
        .Attachments.Add fName
        .Display
    End With

    Application.ScreenUpdating = True
    Set oMail = Nothing
End Sub