Code to PDF and send email with attachement

[christinap]

Hello, I am seeking help with code to pdf a certain tab on my workbook and then send an email with that pdf attached to whatever email address is in cell K1. I have gotten so far as creating a PDF and having it saved to a folder, but can not figure out the email attachment bit. Below is the current code I have to PDF and save the tab.

Sub Button3_Click()


Sheets(2).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Path & "" & Range("B1") & Space(1) & ActiveSheet.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub


Thanks much!

 

[Domenic]

Try...

Sub Button3_Click()

    Dim olApp As Object
    Dim olMItem As Object
    Dim sFileName As String
    Dim sEmailAddress As String


    With Sheets(2)
        sFileName = ActiveWorkbook.Path & "\" & .Range("B1").Value & Space(1) & ActiveSheet.Name & ".pdf"
        sEmailAddress = .Range("K1").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMItem = olApp.CreateItem(0)
    
    With olMItem
        .to = sEmailAddress
        .Subject = "Your Subject Here"
        .body = "Your text here..."
        .attachments.Add sFileName
        .display 'or .send
    End With
    
    Set olApp = Nothing
    Set olMItem = Nothing
    
End Sub

Note that the email will only be displayed. To send the email, replace...

.display

with

.send

Hope this helps!

 

[christinap]

Wow! It is amazing what you can do in excel. Thank you so much. That worked to pop up an email! I have a couple additional questions. Can I have multiple email address recipients? Would I just change the Range value defined above?

Second question: In the body of the email how do I move onto the next line. Ex: Have a greeting "Good Afternoon" and then two lines down have the full sentence?

 

[Domenic]

With regards to multiple recipients, add them to cell K1 so that it looks like this...

john@abc.com; jane@xyz.com; bob@example.com

Then amend the code as follows (changes are in red)...

Sub Button3_Click()

    Dim olApp As Object
    Dim olMItem As Object
    Dim sFileName As String
    Dim sEmailAddress As String
    [COLOR=#ff0000]Dim sBody As String[/COLOR]


    With Sheets(2)
        sFileName = ActiveWorkbook.Path & "\" & .Range("B1").Value & Space(1) & ActiveSheet.Name & ".pdf"
        sEmailAddress = .Range("K1").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=True
    End With
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMItem = olApp.CreateItem(0)
    
    With olMItem
        .to = sEmailAddress
        .Subject = "Your Subject Here"
       [COLOR=#ff0000] sBody = "Good afternoon," & vbCrLf & vbCrLf
        sBody = sBody & "With regards to . . ." & vbCrLf & vbCrLf
        sBody = sBody & "Thank you!"
        .body = sBody[/COLOR]
        .attachments.Add sFileName
        .display 'or .send
    End With
    
    Set olApp = Nothing
    Set olMItem = Nothing
    
End Sub

Hope this helps!