Hi
I have this macro that generates a PDF and attaches it to the email but the name of the PDF is the name of the excel file and the name I want. How do I get rid of the excel file name from the PDF file name? Also is it possible to add yesterdays date to the name of the email and PDF?
Code
Sub AttachActiveSheetPDF()
Dim IsCreated As
Boolean
Dim i As Long
Dim PdfFile As
String, Title As String
Dim OutlApp As
Object
Dim EmailAddr As
String
Dim Cell As Range
' Define PDF
filename
PdfFile =
ActiveWorkbook.FullName
i =
InStrRev(PdfFile, ".")
If i > 1 Then
PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile
& "Report Name" & Range("A27").Value
& " " & Range("E27").Value & ".pdf"
' Export activesheet
as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile,
Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
'Loop through the
rows
For Each Cell In
Range("L17:L26").Cells
If Cell.Value
Like "*@*" Then
EmailAddr
= EmailAddr & ";" & Cell.Value
End If
Next
' Use already open
Outlook if possible
On Error Resume Next
Set OutlApp =
GetObject(, "Outlook.Application")
If Err Then
Set OutlApp =
CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible =
True
On Error GoTo 0
' Prepare e-mail
with PDF attachment
With
OutlApp.CreateItem(0)
' Prepare e-mail
.Subject =
"Email Subject " & Range("A27").Value
& " " & Range("E27").Value & ""
.To =
"[email protected]"
.CC = ""
.Body = "y"
.Attachments.Add
PdfFile
' Try to send
On Error Resume
Next
.Send
Application.Visible = True
If Err Then
MsgBox
"Error- email not sent", vbExclamation
Else
MsgBox
"Email Sent", vbInformation
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Quit Outlook if it
was created by this code
If IsCreated Then
OutlApp.Quit
' Release the memory
of object variable
Set OutlApp =
Nothing
End Sub
Display More
Any help would be appreciated
Cheers