Save as excel and export as pdf and email


New Member
Apr 28, 2021
Office Version
  1. 365
  1. Windows
Hi guys,

I have the below code but i want to now save as excel format but email as pdf, what changes do i need to make please.

Private Sub CommandButton1_Click()
On Error GoTo err_handler
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

Dim currentDate As String
Dim folderPath As String
Dim pdfFileName As String
Dim pdfFullName As String
Dim duplicateNumber As Long

'// Construct the file name
currentDate = Format(Date, "dd-mm-yyyy")
folderPath = "O:\PP Surveyors\Lease Inspections\2021\Completed Surveys 2021\"
pdfFileName = Sheets("Asset Condition Inspection").Range("B3") & Sheets("Asset Condition Inspection").Range("B2").Value & " - " & currentDate
duplicateNumber = 1

'// Check if the file already exists
If Dir(folderPath & pdfFileName & ".pdf") <> "" Then
'// The file exists so append duplicateNumber to the file name and check if that also exists, if it does increment duplicateNumber and test again. Rinse and repeat.
Do While Dir(folderPath & pdfFileName & "-" & Format(duplicateNumber, "000") & ".pdf") <> ""
duplicateNumber = duplicateNumber + 1
'// Construct the new file name
pdfFileName = pdfFileName & "-" & Format(duplicateNumber, "000")
End If

'// Construct the full name (path, file name and extension)
pdfFullName = folderPath & pdfFileName & ".pdf"

'// Export the workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFullName

'// Open Outlook and create a new email
Set oApp = New Outlook.Application
Set oMail = oApp.CreateItem(olMailItem)

With oMail
.To = Sheets("Asset Condition Inspection").Range("D7").Value
.CC = Sheets("Asset Condition Inspection").Range("D6").Value & "; " & Range("D5").Value & "; " & Range("B9").Value & "; " & Range("C9").Value
.Subject = "Asset Condition Inspection: " & pdfFileName
.HTMLBody = strbody & " This report and the inspection to which it refers to, is intended to identify repairs, decorations, maintenance and statutory compliance that you are responsible for under your obligations in your agreement for your pub and sets out suggested action that we believe you should undertake to meet these obligations. It is not nor is it intended to be an exhaustive survey of the condition of the property, a structural survey report, an interim schedule of dilapidations or check on the suitability of statutory certification. You should obtain independent advice from a suitably qualified professional advisor such as Chartered Building Surveyor to advise you about any issues of concern, the structural condition of your property, preventive maintenance, testing of service media and associated plant and equipment and statutory compliance." & .HTMLBody 'This is the text that will appear in the body of the email. Remove it if not needed.
.Attachments.Add Source:=pdfFullName, Type:=xlTypePDF
.Display 'This will display the email so you can review it before sending. If you want to send it automatically replace .Display with .Send
End With

Set oMail = Nothing
Set oApp = Nothing
Exit Sub

'Something has gone wrong, spit out an error messsage
MsgBox "The following error has occured: " & vbNewLine & Err.Number & ": " & Err.Description, vbCritical, "Error!"
GoTo clean_exit

End Sub

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back