PDF and Excel attachment VBA

[Dean Lynch]

Hi Guys,

I am struggling to figure this one out. I have an excel workbook which I am running a Macro which takes the active page and attaches this to an e-mail.
This is fine and works as it should however I then need to attach the same sheet as a PDF to the same e-mail.

Is this even possible?

Appreciate if anyone has any experience on this as everything I have tried has so far failed!

Thanks
Dean

 

[Logit]

.
Yes, it can be done.

First, save the sheet as a PDF. Then, pretty much using the same code for attaching the sheet to your email, you can use the same code to attach the PDF file.

 

[John_w]

Have a look at ActiveSheet.ExportAsFixedFormat, or record a macro to save the sheet as a PDF. Then attach the saved file to the email.

 

[Dean Lynch]

.
Yes, it can be done.

First, save the sheet as a PDF. Then, pretty much using the same code for attaching the sheet to your email, you can use the same code to attach the PDF file.

Thanks for the reply, I have tried to make this work but the pdf file still will not attach, I have used the below code at the bottom of the "email creation" part of the code:-

"Kind Regards" & "
" & Signature
.ATTACHMENTS.Add Destwb.FullName
.ATTACHMENTS.Add.spath.pdf

The fist attaches the excel, the second part is to add the pdf. Spath is already defined to give the PDF a variable name each time the macro is run.

Sorry if it is obvious but I just cannot see it!!

 

[Logit]

.
Here is an example that I use here. It creates a PDF of the sheet. Temporarily stores it on the C Drive. Attachs it to the email, then deletes the PDF file from the C Drive.

Option Explicit


Sub pdf()
Dim wsA As Worksheet, wbA As Workbook, strTime As String
Dim strName As String, strPath As String
Dim strFile As String
Dim strPathFile As String




'On Error GoTo errHandler


    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    


'replace spaces and periods in sheet name
    strName = Replace(wsA.Name, " ", "")
    strName = Replace(strName, ".", "_")
    
'create default name for savng file
    
    strPath = "C:\PDFs\"
    strFile = Sheets("Email").Range("B2").Value
    strPathFile = strPath & strFile




Dim myFolder$
myFolder = "C:\PDFs"
    
    If Dir(myFolder, vbDirectory) = "" Then
         MkDir myFolder
    End If


'export to PDF if a folder was selected
    wsA.ExportAsFixedFormat 0, strPathFile
    
    If Len(Dir$(myFolder)) > 0 Then
        SetAttr myFolder, vbNormal
    End If


'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile




exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub




Sub Mail_workbook_Outlook()


    Dim c As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strPath As String
    Dim FileName As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    strPath = "C:\PDFs\"
    FileName = Dir(strPath & "*.*")


    'On Error Resume Next
    For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = c.Value
            .CC = ""
            .BCC = ""
            .Subject = c.Offset(0, 1).Value
            .Body = "The parts have been placed on today's load sheet and will be processed by EOB today.  The parts have also been transferred to the repository file."
            FileName = Dir(strPath & "*.*")
            .Attachments.Add strPath & FileName
            
            '.Send                              '<-- .Send will auto send email without review
            .Display                            '<-- .Display will show the email first for review
        End With
        'On Error GoTo 0
    Next c




    Set OutMail = Nothing
    Set OutApp = Nothing
    
   byby
      
End Sub


Sub byby()  'deletes PDF file after attaching to email
Dim folder As Object
Dim path As String
path = "C:\PDFs"
Set folder = CreateObject("scripting.filesystemobject")


    folder.DeleteFolder path, True


End Sub

Here is the layout for the sheet :

	A​	B​	C​
1​	Email Addresses​	File Name​
2​	X@YAHOO.COM​	X Email​	​
3​	Y@YAHOO.COM​	Y Email​
4​	Z@YAHOO.COM​	Z Email​