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

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
103
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
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
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
103
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
[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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
Im glad to help you, thanks for the feedback.
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
103
one last tweek.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
Try this

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

Forum statistics

Threads
1,077,777
Messages
5,336,192
Members
399,069
Latest member
haxahid

Some videos you may like

This Week's Hot Topics

Top