PDF exported from Excel into e-mail as attachment

[Terje Fritzman]

Hello,

I've managed to get my excel sheet exported as a PDF and I've been able to attach it into outlook as an attachment if I use a full path to the file, but when I get a little more advanced and make the file name: "ActiveWorkbook.Path & "\Endringsmelding " & Cells(5, 27)", the attachment no longer gets attached in the mail.

Anyone know why my code suddenly doesn't work anymore?

Code:

>>> use code - tags <<<

Private Sub CommandButton1_Click()

'Make PDF and attach in Outlook
ChDir ActiveWorkbook.Path
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveWorkbook.Path & "\Endringsmelding " & Cells(5, 27), OpenAfterPublish:=True

    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim myAttachments As Object
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    Set myAttachments = xOutMail.Attachments
    xMailBody = "Hei," & vbNewLine & vbNewLine & _
              "Her kommer endringsmeldingen ang. ......" & vbNewLine & vbNewLine & _
              "Kunne jeg ha fått tilbake en signert versjon?" & vbNewLine & vbNewLine
                  On Error Resume Next
    With xOutMail
        .To = Cells(5, 41).Value
        .Cc = ""
        .BCC = ""
        .Subject = "Endringsmelding"
        .Body = xMailBody
        .myAttachments.Add (ActiveWorkbook.Path & "\Endringsmelding " & Cells(5, 27))
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing

End Sub

 

[vletm]

Terje Fritzman
One possible test:
You could verify[/solve] Your challenge by adding just after Your .Body = xMailBody -line.
next line:
msgbox ActiveWorkbook.Path & "\Endringsmelding " & Cells(5, 27)
and
You could see its value ... is it 100% correct, including Path.Separators as well as it should end with .pdf?

 

[Terje Fritzman]

Thanks for answering!

That's clever. I added the msgbox and got what the right path, but no attachment. I've added -& ".pdf"- to the end as well, but no luck.

The path seems correct:


The PDF file:

 

[vletm]

Terje Fritzman
1) Alle tegn telles!
2) this sample has some differences

Sub AddAttachment() 
Dim myItem As Outlook.MailItem 
Dim myAttachments As Outlook.Attachments 
Set myItem = Application.CreateItem(olMailItem) 
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\Test.doc"
myItem.Display
End Sub

3) Have You tested it without those parentheses?
4) Have You tested it by 'solve' that path & filename before With xOutMail
APF = ActiveWorkbook.Path & "\Endringsmelding " & Cells(5, 27)
and then .myAttachments.Add APF
5) ... still to make 100% that Your pdf-file is there
5a) Save above APF to one cell
5b) You can test that text with normal browser to check one-more-time that is really okay. If it finds/opens then there would be something else...

 

[Terje Fritzman]

The path works in my browser, so the link should work. I tried with a direct path too, and also moved it from the desktop, but that doesn't work either.

The mail generation works fine, the pdf export works fine and the path is correct. It seems like the Attachment.Add doesn't want to work (But it worked before )

 

[vletm]

Terje Fritzman
One note ... now Your files name is different ( there is % ... instead some spaces ...).
> Try to write code which 'only' adds attachment .. something like in my sent sample.
kanskje i morgen vil lykkes

 

[Terje Fritzman]

Hello,

I started from scratch and used a new code, and now it seems to work

Private Sub CommandButton1_Click()

' Create PDF of active sheet and send as attachment.
'
    Dim strPath As String, strFName As String
    Dim OutApp As Object, OutMail As Object
  
     'Create PDF of active sheet only
    ChDir ActiveWorkbook.Path
    
    strFName = "Endringsmelding " & ActiveSheet.Name & ".pdf"
  
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      
     'Set up outlook
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

     'Create message
    On Error Resume Next
    With OutMail
        .to = Cells(5, 41).Value 'Insert required address here ########
        .CC = ""
        .BCC = ""
        .Subject = "Endringsmelding " & Cells(5, 27)
        .Body = "Hei," & vbNewLine & vbNewLine & _
              "Her kommer endringsmeldingen ang. ......" & vbNewLine & vbNewLine & _
              "Kunne jeg ha fått tilbake en signert versjon?" & vbNewLine & vbNewLine
        .Attachments.Add strFName
        .Display   'Use only during debugging ##############################
        '.Send      'Uncomment to send e-mail ##############################
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

 

[vletm]

Terje Fritzman
Yes ...
... that gives an image that ... path wasn't correct with Your original code ... no matter that it looks correct
There are sometimes this kind of ... interesting ... cases.
... hmm?
Just noticed ... this kind is almost 'normal' situation, while writing a code - especially which creats PDF-files.

Many times 'normal' Excel Quit would solve that.​