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

robgoldstein

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

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,285
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
114
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
10,285
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
10,285
Office Version
2007
Platform
Windows
Im glad to help you, thanks for the feedback.
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
114
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
10,285
Office Version
2007
Platform
Windows
Try this

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

Watch MrExcel Video

Forum statistics

Threads
1,090,481
Messages
5,414,796
Members
403,544
Latest member
rabdorawsh

This Week's Hot Topics

Top