+ Reply to Thread
Results 1 to 4 of 4

Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    Portland
    MS-Off Ver
    2010
    Posts
    3

    Question Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

    Here is my code I got to work, however I have multiple excel files at a time.
    How do I not open the excel doc to run and add a loop to repeat the process for all files in the folder?

    The MSG piece is critical and there is specific information needed in the body, can I add on to this? I understand VB enough, but not enough to figure the rest of this out.

    Thanks
    Angela



    CODE
    ------------------------------------------------------------------

    Sub PDFEmail()

    'Declare variables
    Dim MyFilePath As String
    Dim MyFileName As String
    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem '<-- Early binding
    Dim EmailAddress As String
    Dim EmailSubject As String
    Dim Msg As String


    Application.ScreenUpdating = False

    'Activate the Transaction Form worksheet
    'Worksheets("Transaction Form").Activate

    'Specify email address, email subject, and a brief message
    EmailAddress = "[email protected]"
    EmailSubject = "Please see attached email"
    Msg = Range("A2")

    'Build parts of name of PDF file
    MyFilePath = "C:\Users\matthane\Documents\OLD_ECOM_DATA\Missing"
    'ThisWorkbook.Path & "C:\Users\matthane\Documents\OLD_ECOM_DATA\Missing"
    MyFileName = ActiveSheet.Name

    'Save Transaction Form as PDF file in same directory as this
    'Excel workbook
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    MyFilePath & MyFileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False

    'Create Outlook object
    Set OutlookApp = New Outlook.Application

    'Send out the email
    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
    .To = EmailAddress
    .Subject = EmailSubject
    .Body = Msg
    .Attachments.Add MyFilePath & MyFileName & ".PDF"
    .Send

    End With

    'House cleaning
    Set OutlookApp = Nothing

    MsgBox "Email has been sent. BTW Your coffee and danish are waiting downstairs."

    Application.ScreenUpdating = True

    End Sub

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

    Msg = Range("A2").value

    You would build the macro in your PERSONAL workbook. When you record a macro, it will default to Store MAcro in THIS WORKBOOK. change it to PERSONAL.
    save.
    Now you can run the macro,
    have it open all the files...
    for each file, run the PDFEmail macro.

  3. #3
    Registered User
    Join Date
    08-07-2014
    Location
    Portland
    MS-Off Ver
    2010
    Posts
    3

    Re: Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

    What if I cannot open the files, that is the modification I need to do. How would I loop through all the files? I am stuck there.....

  4. #4
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

    run personal macro EmailAllInDir (foldername)

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-07-2014
    Location
    Portland
    MS-Off Ver
    2010
    Posts
    3

    Re: Convert Excel sheet to PDF and attach in Outlook and LOOP using VBA

    This did not work, it wants me to actually open the excel file.
    The process this person wanted is to export files from access in excel (that part works)
    Then my macro will do the rest......

    What is the best way and cleanest way from start to finish?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert excel sheet to pdf using macro and send that pdf via email using outlook
    By suresh mongam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2015, 08:51 AM
  2. [SOLVED] VBA to Convert Excel sheet to PDF and attach in Outlook
    By lorber123 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-09-2014, 09:53 PM
  3. Macros to open new outlook email and attach the excel sheet
    By nageshpolu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2014, 02:26 PM
  4. Code to attach a sheet as an Outlook email attachment
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-30-2011, 11:32 AM
  5. Attach single sheet to outlook not entire book
    By LiLi1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2010, 09:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1