Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Function DistributeDailyConcept() 'Declare variables Dim MyFilePath As String Dim SavePDFas As String Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem '<-- Early binding Dim EmailAddress As String Dim EmailSubject As String Dim MsgBody1 As String Dim MsgBody2 As String Dim ShipDate As String Dim wkb As Workbook 'Dim xlApp As Excel.Application Excel.Application.Quit Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "C:\Share\Daily Sales\Daily Sales Report by Concept JOM.xlsx", True, False Excel.Application.ScreenUpdating = False Excel.Application.DisplayAlerts = False xlApp.DisplayAlerts = False 'Create Outlook object Set OutlookApp = New Outlook.Application 'Specify email address and email subject EmailAddress = "angela.mXXXXXX@XXXXXX.com" 'EmailAddress = "rick.wXXXXXX@XXXXX.com" EmailSubject = "Daily Sales Report by Concept JOM" 'Build parts of name of PDF file MyFilePath = "C:\Share\Daily Sales" strFile = Dir(MyFilePath & "\Daily Sales Report by Concept JOM.xlsx") 'MyFileName = strFile Set wkb = Excel.Workbooks.Open(MyFilePath & "\" & strFile) 'Specify email message MsgIntro = "Please find the Daily Sales Report by Concept JOM. " MsgBody1 = Sheets("data").Range("M2") MsgBody2 = Sheets("data").Range("M3") MsgFile = "File with all account detail is also saved at C:\Share\Daily Sales\Daily Sales Report by Concept JOM.xlsx" MsgEnd = "If you have any questions, please let us know...Kind Regards, NAM DTC Finance" ShipDate = Sheets("Data").Range("GR") SavePDFas = "C:\Share\Daily Sales\Daily Sales Report by Concept JOM " & ShipDate & ".pdf" 'process the data in the workbook here 'Save Transaction Form as PDF file in same directory as this 'Excel workbook wkb.Save wkb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _ SavePDFas, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Excel.Application.ActiveWorkbook.Save wkb.Close Excel.Application.Quit xlApp.Quit 'Send out the email Set MItem = OutlookApp.CreateItem(olMailItem) With MItem .To = EmailAddress .Subject = EmailSubject & " " & ShipDate .Body = MsgIntro & Chr(10) & Chr(10) & MsgBody1 & Chr(10) & Chr(10) & MsgBody2 & Chr(10) & Chr(10) & MsgFile & Chr(10) & Chr(10) & MsgEnd .Attachments.Add SavePDFas .Send End With 'House cleaning Set OutlookApp = Nothing Set xlApp = Nothing Set XLWbk = Nothing Set XLSht = Nothing End Function
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.