Attaching PDF. to Outlook Email Using Excel

[Tristan Shannon]

Hi,

I have been trying to work on a Macro to automatically send an PDF. attached email from excel.

I found one on the internet and tried to modify for my situation. My current code looks like this:
Sub SendSheetAsPDF()

Dim olApp As Object

Path = "I:\GM projects templates\Quantity Surveyor Templates\Purchase Orders\"
Salesman = ActiveSheet.Name
strDate = Format(Date, "ddmmyyyy")
If i > 1 Then PDF_File = Left(PDF_File, i - 1)
PDF_File = "I:\GM projects templates\Quantity Surveyor Templates\Purchase Orders\" & Range("F9").Value

With ActiveSheet
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
PDF_File _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End With

Set olApp = CreateObject("Outlook.Application")


With olApp.CreateItem(0)
.Subject = "Report"
.To = "tristan@barretthomes.co.nz"
.Body = "Hi," & vbLf & vbLf _
& "The report is attached in PDF format." & vbLf & vbLf _
& "Regards,"
.Attachments.Add PDF_File
.Send


End With

' if you want to delete it
'Kill PDF_File

olApp.Quit
Set olApp = Nothing

End Sub

If I delete the attachment the email sends okay so its purely just to do with the attachment.

Any help would be greatly appreciated I've been going round and round in circles.

Thanks,

Tristan

 

[Chihiro]

Can you upload sample workbook?

Just by looking at your code there are some issues
1. Variable "i" where is this set?
2. You set "Path =" but it's not used subsequently...
3. You use "PDF_File" variable while it's empty... then subsequently set value by hard coding path again...
etc etc.

 

[Tristan Shannon]

Yeah sorry i'm just new to this. I wasn't sure what these variables were. I have attached a copy of the workbook so hopefully this sheds some light on the problems. Thank you very much for your help.

 

[Chihiro]

Here's the code.

Sub SendSheetAsPDF()
Dim ws As Worksheet
Dim olApp As Object

    Set ws = Worksheets("Painting")
    Path = "I:\GM projects templates\Quantity Surveyor Templates\Purchase Orders\"
    strDate = Format(Date, "ddmmyyyy")
    PDF_File = Path & Trim(ws.Range("F9").Value)
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        PDF_File _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    Set olApp = CreateObject("Outlook.Application")


    With olApp.CreateItem(0)
        .Subject = "Report"
        .To = "tristan@barretthomes.co.nz"
        .Body = "Hi," & vbLf & vbLf _
            & "The report is attached in PDF format." & vbLf & vbLf _
            & "Regards,"
        .Attachments.Add PDF_File & ".pdf"
        .Send

 
  End With
  ' if you want to delete it
  'Kill PDF_File
  olApp.Quit
  Set olApp = Nothing
End Sub

Note:
Controls has been moved to separate sheet. As the code exports entire sheet as PDF (and will show control buttons in PDF file).

 

[Tristan Shannon]

It's working! Thank you so much for your help.