Save As pdf and email with outlook

lakeroof

New Member
Joined
Jan 30, 2013
Messages
15
The code I have below works as I want, except
It saves the whole workbook as pdf and I would like to save only "Sheet6"
It also does not insert my signature in outlook which I need
Any help to modify this would be appreciated.

VBA Code:
Sub Email_From_Excel_English()
    Dim emailApplication As Object
    Dim emailItem As Object
    Dim strPath As String
    Dim lngPos As Long
 
    ' Build the PDF file name
    strPath = ActiveWorkbook.FullName
    lngPos = InStrRev(strPath, ".")
    strPath = Left(strPath, lngPos) & "pdf"
    
    ' Export workbook as PDF
    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, strPath
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
    
    ' Now we build the email.
    emailItem.To = Range("C4")
    emailItem.Subject = "Roofing Estimate"
    emailItem.Body = "Please find attached your estimate, as well as a copy of our terms and conditions." & vbNewLine & vbNewLine & " If you have any questions, please do not hesitate to contact me." & vbNewLine & vbNewLine & "Regards," & vbNewLine & vbNewLine & signature
    
    ' Attach the PDF file
    emailItem.Attachments.Add strPath
    emailItem.Attachments.Add "C:\Users\Nick\Dropbox\1Nick\2022_Nick\Terms Conditions ENG.pdf"
    
    ' Send the Email
    ' Use this OR .Display, but not both together.
    emailItem.Display
    
    ' Display the Email so the user can change it as desired before sending it
    ' Use this OR .Send, but not both together.
    'emailItem.Display
    Set emailItem = Nothing
    Set emailApplication = Nothing
    ' Delete the PDF file
    Kill strPath
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top