Mail Excel Sheets as PDF

Copper Contributor

I have this VBA however i want the emails to be sent as PDF and not Excel Sheets and can't change the File Format to PDF . Is there an extra line to be added I tried changing XFile EXT to PDF but that doesn't work .

 

Sub Mail_Every_Worksheet()
'Updateby ExtendOffice
Dim xWs As Worksheet
Dim xWb As Workbook
Dim xFileExt As String
Dim xFileFormatNum As Long
Dim xTempFilePath As String
Dim xFileName As String
Dim xOlApp As Object
Dim xMailObj As Object
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
xTempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
xFileExt = ".xls": xFileFormatNum = -4143
Else
xFileExt = ".xlsm": xFileFormatNum = 52
End If
Set xOlApp = CreateObject("Outlook.Application")
For Each xWs In ThisWorkbook.Worksheets
If xWs.Range("B2").Value Like "?*@?*.?*" Then
xWs.Copy
Set xWb = ActiveWorkbook
xFileName = xWs.Name & " of " _
& VBA.Left(ThisWorkbook.Name, VBA.InStr(ThisWorkbook.Name, ".") - 1) & " "
Set xMailObj = xOlApp.CreateItem(0)
xWb.Sheets.Item(1).Range("B2").Value = ""
With xWb
.SaveAs xTempFilePath & xFileName & xFileExt, FileFormat:=xFileFormatNum
With xMailObj
'specify the CC, BCC, Subject, Body below
.To = xWs.Range("B2").Value
.CC = ""
.BCC = ""
.Subject = "Times for Tomorrows Deliveries"
.Body = "Please find attached times for tomorrows Deliveries"
.Attachments.Add xWb.FullName
.Display
End With
.Close SaveChanges:=False
End With
Set xMailObj = Nothing
Kill xTempFilePath & xFileName & xFileExt
End If
Next
Set xOlApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

1 Reply

@Joanne_Neville1970 A quick search in this forum for save PDF finds posts such as Saving worksheet as pdf with worksheet name and date stamp.  The first two such posts that I read both used the ExportAsFixedFormat method of the Worksheet object to create a PDF, rather than the SaveAs method of the Workbook object.  (A method of that same name is also available for the Workbook object, if you should need to use that.)