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

[amatthey72]

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 = "Angela.Matthey@adidas.com"
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

[ranman256]

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.

[amatthey72]

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.....

[ranman256]

run personal macro EmailAllInDir (foldername)

Please Login or Register  to view this content.

[amatthey72]

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?