automate save workbook as PDF and email to a fixed address.

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
165
Office Version
  1. 2019
Platform
  1. Windows
Has anyone written a script that would save the 4 sheets of a workbook as a 4 page PDF and then email that PDF to a specific address from my gmail address?

Ideally I would like that PDF file to have today's date in the name.
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try this. Change data in red for your information.

Code:
Sub Send_sheets()
  Dim Mail As New Message, Config As Configuration
  Dim eMail As String, ePass As String, wPath As String, wFile As String
  'Your data
  eMail = "[COLOR=#ff0000]user@gmail.com[/COLOR]"
  ePass = "[COLOR=#ff0000]pwd[/COLOR]"
  'Set config mail
  Set Config = Mail.Configuration
  Config(cdoSendUsingMethod) = cdoSendUsingPort
  Config(cdoSMTPServer) = "smtp.gmail.com"
  Config(cdoSMTPServerPort) = 465
  Config(cdoSMTPAuthenticate) = cdoBasic
  Config(cdoSMTPUseSSL) = True
  Config(cdoSendUserName) = eMail
  Config(cdoSendPassword) = ePass
  Config.Fields.Update
  'Save file pdf
  wPath = ThisWorkbook.Path & "\"
  wFile = wPath & Format(Date, "dd-mm-yyyy") & ".pdf"
  Sheets(Array("[COLOR=#ff0000]Sheet1[/COLOR]", "[COLOR=#ff0000]Sheet2[/COLOR]", "[COLOR=#ff0000]Sheet3[/COLOR]", "[COLOR=#ff0000]Sheet4[/COLOR]")).Copy
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  ActiveWorkbook.Close False
  'Send mail
  Mail.To = "[COLOR=#ff0000]addressmail[/COLOR]@yahoo.com"
  Mail.From = eMail
  Mail.subject = "[COLOR=#ff0000]Your Subject[/COLOR]"
  Mail.textBody = "Your body"
  Mail.AddAttachment wFile
  On Error Resume Next
  Mail.Send
End Sub
 
Upvote 0
sadly I am getting an error right off the start. "User defined type not defined" on line 1.

I also changed the subject to reference a cell in sheet 1. Not sure I did that properly.



Code:
Sub Send_sheets()
  Dim eMail As New Message, Config As Configuration
  Dim eMail As String, ePass As String, wPath As String, wFile As String
  'Your data
  eMail = "sctoronto2011boys@gmail.com"
  ePass = "2011soccer"
  'Set config mail
  Set Config = Mail.Configuration
  Config(cdoSendUsingMethod) = cdoSendUsingPort
  Config(cdoSMTPServer) = "smtp.gmail.com"
  Config(cdoSMTPServerPort) = 465
  Config(cdoSMTPAuthenticate) = cdoBasic
  Config(cdoSMTPUseSSL) = True
  Config(cdoSendUserName) = eMail
  Config(cdoSendPassword) = ePass
  Config.Fields.Update
  'Save file pdf
  wPath = ThisWorkbook.Path & "\"
  wFile = wPath & Format(Date, "dd-mm-yyyy") & ".pdf"
  Sheets(Array("Team 1", "Team 2", "Team 3")).Copy
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wFile, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  ActiveWorkbook.Close False
  'Send mail
  Mail.To = "robgoldstein@gmail.com"
  Mail.From = eMail
  Mail.Subject = "Game Sheets for Weekend of (Worksheets("Team 1").Range("B13"))"
  Mail.textBody = "Here are the gamesheets for this weekend"
  Mail.AddAttachment wFile
  On Error Resume Next
  Mail.Send
End Sub
 
Upvote 0
[FONT=&quot]Use the [/FONT]Tools->References[FONT=&quot] menu in the VBA-Editor.
[/FONT]

Search Microsoft CDO for Windows 2000 and select check box.
 
Upvote 0
Im glad to help you, thanks for the feedback.
 
Upvote 0
one last tweek.

How would I add the date (which is cell B13 in sheet "team 1" to the text in the subject line?
 
Upvote 0
Try this

Code:
Mail.Subject = "Game Sheets for Weekend of " & Worksheets("Team 1").Range("B13").Value
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top