Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Help with Code to convert excel files to PDF then email (code included). How can I run without active sheet open?

Avatar of gracie1972
gracie1972Flag for United States of America asked on
Microsoft OfficeMicrosoft ExcelVB Script
31 Comments1 Solution369 ViewsLast Modified:
Trying to convert and email PDF's, I want to loop and convert more than one file at a time in a folder:

HELP please :)

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 = "Angela.XXXX@XXXXXXX.com"
    EmailSubject = "Please see attached email"
    Msg = Range("A2")

    'Build parts of name of PDF file
    MyFilePath = 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
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimarkFlag of United States of America imageGet vaccinated; Social distance; Wear a mask
Commented:
This problem has been solved!
Unlock 1 Answer and 31 Comments.
See Answers