Excel 2010 to send email with attached PDF 1

[imquazar]

Excel 2010 in a corporate environment.
Windows XP SP3

Issue: Open an excel spreadsheet that has 6 columns of information to be entered. (1 row to say, 50 rows).
I've created a macro button to "Send it" which when I recorded the macro, I went through the entire process up to and including sending the email. When I'm done, I see where it exported the file as a PDF, saving it to my C: Drive, however when I hit the button, it sets the email up with the attachment as an excel spreadsheet.

I'm sure this is something simple, but when I look through the code, I'm not seeing where it's pulling the file and attaching it to the email.

Any ideas? Here's the block of code: (I've modified out the sensitive info)

Sub SendPDF()
'
' SendPDF Macro
' Sends email with PDF Attachment
'
'
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\DOCUME~1\<userID>\LOCALS~1\Temp\<Filename>.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.Dialogs(xlDialogSendMail).Show _
arg1:="<recipient>", _
arg2:="<Subject>"
End Sub

Any help is appreciated.
Thanks!
-IQ

 

[mintjulep]

Looks like your code exports a copy as PDF.

Then it sends the spreadsheet as email.

Two separate and unrelated tasks.

So what is happening is what you are asking it to do.

You need to:

Export the PDF.

Create an email.

Attach the PDF.

Send.

 

[firefytr]

Hi there,

You could use something a little more efficient. This should run a little faster for you. It cleans up the PDF file afterwards and displays the email with it attached. Set the subject and recipient(s) as desired.

Option Explicit

Sub EmailAsPDF()

    Dim WB                      As Workbook
    Dim WS                      As Worksheet
    Dim sPath                   As String
    Dim sPDFName                As String
    Dim OL                      As Object
    Dim olMail                  As Object
    Dim bOLOpen                 As Boolean

    Call TOGGLEEVENTS(False)
    
    Set WB = ThisWorkbook

    '////////// SET THE WORKSHEET NAME HERE //////////
    Set WS = WB.Worksheets("Sheet1")

    sPath = ThisWorkbook.Path & "\"
    sPDFName = ThisWorkbook.Name
    If sPDFName Like "*.xls*" Then
        sPDFName = Left(sPDFName, InStrRev(sPDFName, ".") - 1) & ".pdf"
    End If

    If Dir(sPath & sPDFName, vbNormal) <> vbNullString Then
        Kill sPath & sPDFName
    End If
    
    WS.ExportAsFixedFormat Type:=xlTypePDF, _
                           Filename:=sPath & sPDFName, _
                           Quality:=xlQualityStandard, _
                           IncludeDocProperties:=True, _
                           IgnorePrintAreas:=False, _
                           OpenAfterPublish:=False

    On Error Resume Next
    Set OL = GetObject(, "Outlook.Application")
    bOLOpen = True
    If OL Is Nothing Then
        Set OL = CreateObject("Outlook.Application")
        bOLOpen = False
    End If
    
    Set olMail = OL.CreateItem(0)
    olMail.Attachments.Add sPath & sPDFName
    olMail.Subject = "Subject"
    olMail.To = "to@domain.com"
    olMail.Display
    
    If bOLOpen = False Then OL.Quit
    
    Kill sPath & sPDFName
    Set OL = Nothing
    Set olMail = Nothing
    
    Call TOGGLEEVENTS(True)
    
End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci