PDA

View Full Version : Convert to PDF, SaveAs, Attach to Outlook Email



foxyginger
09-07-2017, 01:22 PM
When I try to attach the newly created PDF to the Outlook email, it can't find the document in my files. What string/file path do I use to attach the PDF with FileName:=Range("B3").Value & " " & Format(Range("B4").Value, "mm-dd-yyyy") & " " & "Wire Information" to the Outlook email?




Sub WireTransferInfoPDF_EmailAttachment()
ActiveWindow.SmallScroll Down:=36
Range("B47:J76").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("A3:I28").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B4").Select
Selection.NumberFormat = "m/d/yyyy"
Range("C1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Font
.Name = "Calibri"
.Size = 13
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With

Range("C6").Select

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B3").Value & " " & Format(Range("B4").Value, "mm-dd-yyyy") & " " & "Wire Information", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False 'THIS IS THE PDF FILE I'M TRYING TO ATTACH TO THE EMAIL BELOW

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("OutLook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = ""
.Subject = "Equity ETL and Wire Information"
.Body = "Hi," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add ' ERROR MESSAGE -- How do I define the file path to find the newly created PDF [Data Source Needed]
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

mdmackillop
09-08-2017, 01:13 PM
Sub WireTransferInfoPDF_EmailAttachment()
Dim PDF
PDF = Range("B3").Value & " " & Format(Range("B4").Value, "mm-dd-yyyy") & " " & "Wire Information.pdf"
'......
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF, _
Quality:=xlQualityStanda0rd, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'......

.Body = "Hi," & vbNewLine & vbNewLine & "Please process the attached Equity ETL." & vbNewLine & vbNewLine & "Thank you,"
myAttachments.Add PDF
.Display
'......