10 Comments

1. 

   Yousef Qaroos on July 14, 2017 at 9:37 am

   Is there any way to send the ActiveSheet as an attachment and pdf in same outlook Email message?

   Reply
   • 

     Vishwamitra Mishra on July 14, 2017 at 1:21 pm

     Yes ofcourse, you can refer these two articles and combine the codes for creating PDF out of activesheet and attaching Active sheet.

     http://learnexcelmacro.com/wp/2012/08/mail-one-sheet/
     http://learnexcelmacro.com/wp/2012/11/send-worksheet-as-a-pdf-attachment/
     [code language=”vb”]
     Sub Email_One_ActiveSheet()

     ‘Do not forget to change the email ID
     ‘before running this code

     Dim OlApp As Object
     Dim NewMail As Object
     Dim TempFilePath As String
     Dim FileExt As String
     Dim TempFileName As String
     Dim FileFullPath As String
     Dim FileFormat As Variant
     Dim Wb1 As Workbook
     Dim Wb2 As Workbook

     With Application
     .ScreenUpdating = False
     .EnableEvents = False
     End With
     Set Wb1 = ThisWorkbook
     ActiveSheet.Copy
     Set Wb2 = ActiveWorkbook

     ‘Below code will get the File Extension and
     ‘the file format which we want to save the copy
     ‘of the workbook with the active sheet.

     With Wb2
     If Val(Application.Version) < 12 Then
     FileExt = ".xls": FileFormat = -4143
     Else
     Select Case Wb1.FileFormat
     Case 51: FileExt = ".xlsx": FileFormat = 51
     Case 52:
     If .HasVBProject Then
     FileExt = ".xlsm": FileFormat = 52
     Else
     FileExt = ".xlsx": FileFormat = 51
     End If
     Case 56: FileExt = ".xls": FileFormat = 56
     Case Else: FileExt = ".xlsb": FileFormat = 50
     End Select
     End If
     End With

     ‘Save your workbook in your temp folder of your system
     ‘below code gets the full path of the temporary folder
     ‘in your system

     TempFilePath = Environ$("temp") & "\"

     ‘Now append a date and time stamp
     ‘in your new file

     TempXLFileName = Wb1.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss")

     ‘THis is for your PDF file
     TempPDFFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"

     ‘Complete path of the file where it is saved
     xlFileFullPath = TempFilePath & TempXLFileName & FileExt

     ‘PDF file path
     pdfFileFullPath = TempFilePath & TempPDFFileName

     ‘Now save your currect workbook at the above path
     Wb2.SaveAs xlFileFullPath, FileFormat:=FileFormat

     ‘Now Export the Activesshet as PDF with the given File Name and path
     With ActiveSheet
     .ExportAsFixedFormat _
     Type:=xlTypePDF, _
     Filename:=pdfFileFullPath, _
     Quality:=xlQualityStandard, _
     IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, _
     OpenAfterPublish:=False
     End With

     ‘Now open a new mail

     Set OlApp = CreateObject("Outlook.Application")
     Set NewMail = OlApp.CreateItem(0)

     On Error Resume Next
     With NewMail
     .To = "info@learnexcelmacro.com"
     .CC = "info@learnexcelmacro.com"
     .BCC = "info@learnexcelmacro.com"
     .Subject = "Type your Subject here"
     .Body = "Type the Body of your mail"
     .Attachments.Add xlFileFullPath ‘— full path of the temp xl file where it is saved
     .Attachments.Add pdfFileFullPath ‘— full path of the temp PDF file where it is saved
     .send ‘or use .Display to show you the email before sending it.
     End With
     On Error GoTo 0

     ‘Since mail has been sent with the attachment
     ‘Now close and delete the temp file from the
     ‘temp folder
     Wb2.Close SaveChanges:=False
     Kill xlFileFullPath
     Kill pdfFileFullPath

     ‘set nothing to the objects created
     Set NewMail = Nothing
     Set OlApp = Nothing

     ‘Now set the application properties back to true
     With Application
     .ScreenUpdating = True
     .EnableEvents = True
     End With
     End Sub

     [/code]

     Reply
2. 

   Alex on August 15, 2017 at 4:24 pm

   Hello,

   This code works 99% perfect for me. I’m trying to get the code to save a specific sheet as an attachment and have tried using the code you suggested:

   ActiveSheet.Copy => Sheets(“Sheet_Name”).Copy

   however it comes up with a syntax error due to the “=>”. I changed this to be just “=” so it becomes this:

   ActiveSheet.Copy = Sheets(“Sheet_Name”).Copy

   When I go to run the code, it comes up with “Run-time error ‘9’: Subscript out of range”

   Do you have any suggestions for how to resolve this?

   Thank you for your help!

   Reply
   • 

     Alex on August 15, 2017 at 4:33 pm

     I’ve modified the code to remove ActiveSheet.Copy = to be just:

     Sheets(“Sheet_Name”).Copy

     Am still getting the Run-Time error ‘9’: Subscript out of range however

     Reply
     • 

       test on October 13, 2017 at 9:24 am

       Have you managed to resolve this?
       I think what the author meant is to replace ActiveSheet.Copy in the code with Sheets(“yoursheetname”).Copy
       Instead of ‘Sheet_Name’ you should insert the name of sheet you wish to attach.

       Reply
3. 

   Bilal on September 15, 2018 at 6:57 am

   I have replaced ActiveSheet.Copy in the code with Sheets(“mysheetname”), but got an error as highlighted by Other Users

   Reply
4. 

   EJ van der Beek on September 27, 2018 at 12:48 pm

   I am searching for a macro which instead of outlook operate to Thunderbird.

   Reply
5. 

   Barbara on October 11, 2019 at 2:51 pm

   I would like to have this code in my personal macro workbook, but send a tab from another workbook via e-mail. Using above code I keep sending my personal macro workbook instead. How can I get around this?

   Reply
6. 

   Zach on August 2, 2020 at 10:42 pm

   How might I select just a range of cells to add to the new workbook, or simply use the PrintArea as the range?

   Reply
   • 

     Zach on August 3, 2020 at 12:25 am

     I’d also like for the newly created worksheet to be sent with the macro’s intact

     Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.