PDF attachment to email

Hi All

I have a problem with adding an attachment to an email . It's been driving me mad for days.

I can create the email no problem but the email is just a part of a macro which saves a sheet from the workbook to pdf and sends it to a folder and then clears the form for the next entry. The problem I'm having as attaching the saved pdf to the email. I've tried every combination of address for the file imaginable without success to create the attachment. I've added the code to see if anyone can see where I'm going wrong. I've left my last attempt at the address in the .Attachments line for you to see

Kind regards

Paul

Option Explicit
    
Sub SaveAsPDF()
    'save OFGEM Report as pdf to OFGEM Report Folder
    Dim strFileName As String
    strFileName = "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & Worksheets("OFGEM Report").Range("AE1").Value
    Worksheets("OFGEM Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        
emailtoinfo

End Sub
    

Sub emailtoinfo()
'Declare and establish the object variables for Outlook.

Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)


'Activate or open Outlook.
On Error Resume Next
AppActivate ("Outlook")
If Err.Number <> 0 Then objInbox.Display
Err.Clear

'Create MailItem email object.
With objMailItem
.To = "info@solarswitching.com"
.Subject = "Ofgem Report: " & "  " & Worksheets("OFGEM Report").Range("AE1").Value
.Body = "Hi Carol" & vbCrLf & vbCrLf & "Please find attached Ofgem Report for " & Worksheets("OFGEM Report").Range("AE1").Value & vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & "Paul"
.Attachments.Add "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & Worksheets("OFGEM Report") & Range("AE1").Value.xlTypePDF \ ""
.Display
End With

'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing

clearcontents

End Sub


Sub clearcontents()
    ' clear contents of input sheet
    Range("a3:bb3").clearcontents
End Sub

Hi Paul,
Please refer to this thread.
http://www.excelforum.com/excel-prog...ml#post3148447
I was helped by JBeaucaire
It may help
Kind regards

You may try:

Sub SaveAsPDF()
'save OFGEM Report as pdf to OFGEM Report Folder
   Dim strFileName                 As String
   Dim strSubject                  As String
   strSubject = Worksheets("OFGEM Report").Range("AE1").Value
   strFileName = "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & strSubject
   Worksheets("OFGEM Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, _
                           Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                              IgnorePrintAreas:=False, OpenAfterPublish:=False

   emailtoinfo strSubject, strFileName

End Sub


Sub emailtoinfo(strSubject As String, strFile As String)
'Declare and establish the object variables for Outlook.

   Dim objOutlook                  As Object
   Dim objNameSpace                As Object
   Dim objInbox                    As Object
   Dim objMailItem                 As Object
   Set objOutlook = CreateObject("Outlook.Application")
   Set objNameSpace = objOutlook.GetNamespace("MAPI")
   Set objInbox = objNameSpace.Folders(1)
   Set objMailItem = objOutlook.CreateItem(0)


   'Activate or open Outlook.
   On Error Resume Next
   AppActivate ("Outlook")
   If Err.Number <> 0 Then objInbox.Display
   Err.Clear

   'Create MailItem email object.
   With objMailItem
      .To = "info@solarswitching.com"
      .Subject = "Ofgem Report: " & "  " & strSubject
      .Body = "Hi Carol" & vbCrLf & vbCrLf & "Please find attached Ofgem Report for " & strSubject & _
                                    vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & "Paul"
      .Attachments.Add strFile
      .Display
   End With

   'Release object variables from system memory.
   Set objOutlook = Nothing
   Set objNameSpace = Nothing
   Set objInbox = Nothing
   Set objMailItem = Nothing

   clearcontents

End Sub


Sub clearcontents()
' clear contents of input sheet
   Range("a3:bb3").clearcontents
End Sub

Hi All

I've just been going through your replies, I can see how they are supposed to work . I've tried your code Izandol as it was the easiest to implement. But it still will not add the attachment???

I noticed in your code Sean an item:

'   Copies sheet1 and Calls function Create_PDF to create a pdf copy and place in e-mail
'   Uses early binding
'   Requires a reference to the Outlook 12.0 Object Library
    Dim OutlookApp As Outlook.Application
    Dim MItem As Object
    Dim Recipient As String, Subj As String
    Dim Msg As String, Fname As String
    Dim EmailAddr As String
    Dim NewFilename As String
            
    Application.ScreenUpdating = False

Could this be what is missing from my code? Or could it be something in the settings? I'm running on widows 8.1 and office 2013

Regards

Paul

Does the value in cell AE1 have the ".pdf" extension included?

The value in cell AE1 of the Ofgem Report sheet is just the job number. It is imported (via a linking formula from the input sheet of the same workbook)

it is added to the file name for identification and into the email for reference.

Your pathing is probably written incorrectly.

'.Attachments.Add "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & Worksheets("OFGEM Report") & Range("AE1").Value.xlTypePDF \ ""

msgbox "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & Worksheets("OFGEM Report") & Range("AE1").Value.xlTypePDF \ ""


debug.print "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & Worksheets("OFGEM Report") & Range("AE1").Value.xlTypePDF \ ""

' Does that look like the file path of the pdf? If it does then copy that output debug and put that in your Run. If it is properly written it will open the pdf from RUN.

I suggest you change one line:

strFileName = "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & strSubject

to this:

strFileName = "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & strSubject & ".pdf"

If there are still errors, remove the On Error Resume Next line and try to run the code again.

Hi Izandol

You are a genius!! worked perfectly

For future reference for everyone here is the final code.

This code does the following:

Saves the sheet to a folder as a pdf. with the correct file name (this is the job number in AE1)

It then creates an email with the correct Reference in the Subject field and adds the reference to the content of the email

It attaches the pdf to the email

It clears the entry sheet ready for the next input.

Thanks to everyone for their help

Kind regards

Paul

I have one more thing that I'd like to achieve with this workbook but it's a different subject so I'll put it in another post

Sub SaveAsPDF()
'save OFGEM Report as pdf to OFGEM Report Folder
   Dim strFileName                 As String
   Dim strSubject                  As String
   strSubject = Worksheets("OFGEM Report").Range("AE1").Value
   strFileName = "C:\Users\paulalexanderbreen\Desktop\OFGEM Reports\OFGEM Report " & strSubject & ".pdf"
   Worksheets("OFGEM Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, _
                           Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                              IgnorePrintAreas:=False, OpenAfterPublish:=False

   emailtoinfo strSubject, strFileName

End Sub


Sub emailtoinfo(strSubject As String, strFile As String)
'Declare and establish the object variables for Outlook.

   Dim objOutlook                  As Object
   Dim objNameSpace                As Object
   Dim objInbox                    As Object
   Dim objMailItem                 As Object
   Set objOutlook = CreateObject("Outlook.Application")
   Set objNameSpace = objOutlook.GetNamespace("MAPI")
   Set objInbox = objNameSpace.Folders(1)
   Set objMailItem = objOutlook.CreateItem(0)


   'Activate or open Outlook.
   On Error Resume Next
   AppActivate ("Outlook")
   If Err.Number <> 0 Then objInbox.Display
   Err.Clear

   'Create MailItem email object.
   With objMailItem
      .To = "info@solarswitching.com"
      .Subject = "Ofgem Report: " & "  " & strSubject
      .Body = "Hi Carol" & vbCrLf & vbCrLf & "Please find attached Ofgem Report for " & strSubject & _
                                    vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & "Paul"
      .Attachments.Add strFile
      .Display
   End With

   'Release object variables from system memory.
   Set objOutlook = Nothing
   Set objNameSpace = Nothing
   Set objInbox = Nothing
   Set objMailItem = Nothing

   clearcontents

End Sub


Sub clearcontents()
' clear contents of input sheet
   Range("a3:bb3").clearcontents
End Sub

Final message

how do i mark this as solved?

According to the Forum Rules:

Click Thread Tools above your first post, select "Mark your thread as Solved"