Sending email and adding 2 attachments from excel

[willow1985]

I have 2 codes, one that creates a pdf and the other that sends an email.
I merged them below but want to add 2 pdf attachments to the email: the one I just created and another located in the same directory.
(Note: if that file is not found, a msg stating this would be preferred just in case of error)

Attachments I want to add are located in the same path: Sheets("CI Form Open").Range("T1")
Name of #1 is in: T2.pdf
Name of #2 is in: N2.pdf

Any help would be greatly appreciated!

Sub SavePDF_send mail()
'
' SavePDF_send mail Macro
'
Dim Path As String
Dim filename As String

Path = Sheets("CI Form Open").Range("T1")
filename = Sheets("CI Form Open").Range("T2")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename & ".pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True


 Dim OutlookApp As Object, MItem As Object
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  With MItem
    .to = Sheets("CI Form Open").Range("T3")
    .Subject = Sheets("CI Form Open").Range("N2")
    .Body = "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"
    .Display
    .Send
  End With
MsgBox "E-mail Sent"
  Case vbNo
        GoTo Quit:
    End Select

Quit:

 

[AC PORTA VIA]

Sub SavePDF_send_mail()
' SavePDF_send mail Macro
'
Dim Path As String
Dim filename As String


Path = Sheets("sheet2").Range("T1")
filename = Sheets("sheet2").Range("T2")


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & "\" & filename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False


 Dim OutlookApp As Object, MItem As Object
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  On Error GoTo MSG
  With MItem
    .to = Sheets("sheet2").Range("T3")
    .Subject = Sheets("sheet2").Range("N2")
    .Body = "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"


    .Attachments.Add Path & "\" & filename & ".pdf"
    .Attachments.Add Path & "\" & "N2" & ".pdf"
 
    .Display
  End With
MsgBox "E-mail Sent"
Exit Sub
MSG: MsgBox " No N2 file found"
End Sub

 

[willow1985]

Sub SavePDF_send_mail()
' SavePDF_send mail Macro
'
Dim Path As String
Dim filename As String


Path = Sheets("sheet2").Range("T1")
filename = Sheets("sheet2").Range("T2")


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & "\" & filename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False


 Dim OutlookApp As Object, MItem As Object
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  On Error GoTo MSG
  With MItem
    .to = Sheets("sheet2").Range("T3")
    .Subject = Sheets("sheet2").Range("N2")
    .Body = "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"


    .Attachments.Add Path & "\" & filename & ".pdf"
    .Attachments.Add Path & "\" & "N2" & ".pdf"
 
    .Display
  End With
MsgBox "E-mail Sent"
Exit Sub
MSG: MsgBox " No N2 file found"
End Sub

I only get the error message No file found but the file is present in the folder (see below code and screenshots)
Don't know where the error is occurring...

Sub SavePDF_send_mail()
' SavePDF_send mail Macro
'
Dim Path As String
Dim filename As String


Path = Sheets("CI Form Open").Range("T1")
filename = Sheets("CI Form Open").Range("T2")


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & "\" & filename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False


 Dim OutlookApp As Object, MItem As Object
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  On Error GoTo MSG
  With MItem
    .to = Sheets("CI Form Open").Range("T3")
    .Subject = Sheets("CI Form Open").Range("N2")
    .Body = "[This is an Automated Message - Do not reply]" & vbCrLf & "Continuous Improvement Database"


    .Attachments.Add Path & "\" & filename & ".pdf"
    .Attachments.Add Path & "\" & "N2" & ".pdf"
 
    .Display
  End With
MsgBox "E-mail Sent"
Exit Sub
MSG: MsgBox " No IAR file found" 'Only get this message but file is present'
End Sub

 

[AC PORTA VIA]

change below "n2" to the file name already in folder

.Attachments.Add Path & "\" & "N2" & ".pdf"

 

[willow1985]

change below "n2" to the file name already in folder

.Attachments.Add Path & "\" & "N2" & ".pdf"

The file name is subject to change so I require a cell reference. Can this be done with a cell reference?

 

[willow1985]

I figured it out. Thank you

.Attachments.Add Path & "\" & Sheets("CI Form Open").Range("N2") & ".pdf"

 

[AC PORTA VIA]

Yes
as long as you capture path- example T4 shows C:\Users\xxxxx\Desktop\files\Daily.pdf

.Attachments.Add Range("T4").Value

 

[AC PORTA VIA]

glad you it works for you