Tuesday, 2 September 2014

How to Send Active Sheet as a PDF Attachment Via Through Outlook Email

Excel VBA Macro to Send Active Sheet as a PDF Attachment Via Through Outlook Email
The following Macro will send the Active Sheet as a Attachment through Outlook in PDF Format.
Sub Email_ActiveSheet_As_PDF()    
'Do not forget to change the Email ID before running this code.
    Dim OutlookApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String
       
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
' Temporary file path where pdf file will be saved before sending it in email by attaching it.    
    TempFilePath = ActiveWorkbook.Path & "\"
    
' Now append a date and time stamp in your pdf file name. 
' Naming convention can be changed based on your requirement.    
TempFileName = ActiveSheet.Name & "_" & Format(Now, "dd-mmm-yyyy") & ".pdf"
    
' Complete path of the file where it is saved.    
FileFullPath = TempFilePath & TempFileName
      
' Now Export the Activesshet as PDF with the given File Name and path.
    
    On Error GoTo ErrMsg:
    With ActiveSheet
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With
    
' Now open a new mail.
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set NewMail = OutlookApp.CreateItem(0)
    
    On Error Resume Next
    With NewMail
        .To = "your_email@domain.com"
        .CC = ""
        .BCC = ""
        .Subject = "Type your Subject here"
        .Body = "Type your Message body here"
        .Attachments.Add FileFullPath '--- Full path of the pdf where it is saved.
        .Send   '--Or use .Display to show you the email before sending it.
    End With
    
    On Error GoTo ErrMsg:
    
' Since mail has been sent with the attachment.
' Now delete the pdf file from the temp folder.
    
    Kill FileFullPath
    
' Set nothing to the objects created.
    Set NewMail = Nothing
    Set OutlookApp = Nothing
    
' Now set the application properties back to true.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox "Email has been Sent Successfully", vbOKOnly, "Job Done"
    Exit Sub

ErrMsg:
        MsgBox err.Description
End Sub

Thanks,
TAMATAM
        [ BI-Developer ]


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts