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

[robgoldstein]

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.

 

[DanteAmor]

Try this. Change data in red for your information.

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]

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.

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]

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

 

[robgoldstein]

Fixed. Thanks so much again.

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[robgoldstein]

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]

Try this

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