VBA to PDF help needed

E

excelwest

New Member
Joined
Jul 29, 2013
Messages
14
The current code below works fine except I need the data from the 'CIF-Master' to paste in the body of the email as well. Currently it only pastes the charts(all the other tabs in the Sheets array). I pasted the entire code below, basically it copies all the chart tabs to a PDF and also embeds them in the body. Any help would be greatly appreciated. Thanks.

'called via control t
Sub UpdateAll()
Call ProcessFile(True, True)
End Sub


Sub ProcessFile(GeneratePDF As Boolean, ComposeEmail As Boolean)

Dim SelectedPath As String


SelectedPath = "C:\Users\jason\Downloads"


Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Check for prior tmp sheet
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name = "Tmp" Then WS.Delete
Next
'Create temp worksheet
Set WS = ThisWorkbook.Sheets.Add
WS.Name = "Tmp"


'Create a variable for PDF file
Dim PDFDate As String
PDFDate = Format(Now(), "mm-dd-yy")

Dim ChartObj As ChartObject
Dim ChartObj2 As Shape
Dim WSShape As Shape

Dim Position As Integer
Position = 2

Dim t As Integer
Dim y As Integer
Dim z As Integer

Dim emailBody As String

'text at top of email
emailBody = "<html><body lang=""EN-US"" link=""#0563C1"" vlink=""#954F72"">"
emailBody = emailBody & "<div class=""WordSection1"">"
emailBody = emailBody & "<p class=""MsoNormal"" style='background:white'>"
emailBody = emailBody & "<b><u><span style='font-size:14.0pt;color:#222222'>CORN:</span></u></b><o:p></o:p></p>"
emailBody = emailBody & "<p class=""MsoNormal"" style='margin-left:.5in'>"
emailBody = emailBody & "<span style='font-size:14.0pt;font-family:Symbol;color:#222222'>·</span>"
emailBody = emailBody & "<span style='font-size:7.0pt;font-family:""Times New Roman"",serif;color:#222222'>         </span>"
emailBody = emailBody & "<b><span style='font-size:14.0pt;font-family:""Arial"",sans-serif;color:#222222'> Firmer in a few locations</span></b><o:p></o:p></p>"

emailBody = emailBody & "<div class=""WordSection1"">"
emailBody = emailBody & "<p class=""MsoNormal"" style='background:white'>"
emailBody = emailBody & "<b><u><span style='font-size:14.0pt;color:#222222'>BEANS:</span></u></b><o:p></o:p></p>"
emailBody = emailBody & "<p class=""MsoNormal"" style='margin-left:.5in'>"
emailBody = emailBody & "<span style='font-size:14.0pt;font-family:Symbol;color:#222222'>·</span>"
emailBody = emailBody & "<span style='font-size:7.0pt;font-family:""Times New Roman"",serif;color:#222222'>         </span>"
emailBody = emailBody & "<b><span style='font-size:14.0pt;font-family:""Arial"",sans-serif;color:#222222'> Weaker in numerous locations</span></b><o:p></o:p></p>"

emailBody = emailBody & "<div class=""WordSection1"">"
emailBody = emailBody & "<p class=""MsoNormal"" style='background:white'>"
emailBody = emailBody & "<b><u><span style='font-size:14.0pt;color:#222222'>WHEAT:</span></u></b><o:p></o:p></p>"
emailBody = emailBody & "<p class=""MsoNormal"" style='margin-left:.5in'>"
emailBody = emailBody & "<span style='font-size:14.0pt;font-family:Symbol;color:#222222'>·</span>"
emailBody = emailBody & "<span style='font-size:7.0pt;font-family:""Times New Roman"",serif;color:#222222'>         </span>"
emailBody = emailBody & "<b><span style='font-size:14.0pt;font-family:""Arial"",sans-serif;color:#222222'> Flat</span></b><o:p></o:p></p>"

emailBody = emailBody & "<div class=""WordSection1"">"
emailBody = emailBody & "<p class=""MsoNormal"" style='background:white'>"
emailBody = emailBody & "<b><u><span style='font-size:14.0pt;color:#222222'>MEAL:</span></u></b><o:p></o:p></p>"
emailBody = emailBody & "<p class=""MsoNormal"" style='margin-left:.5in'>"
emailBody = emailBody & "<span style='font-size:14.0pt;font-family:Symbol;color:#222222'>·</span>"
emailBody = emailBody & "<span style='font-size:7.0pt;font-family:""Times New Roman"",serif;color:#222222'>         </span>"
emailBody = emailBody & "<b><span style='font-size:14.0pt;font-family:""Arial"",sans-serif;color:#222222'> Chicago firmer</span></b><o:p></o:p></p>"

emailBody = emailBody & "<div class=""WordSection1"">"
emailBody = emailBody & "<p class=""MsoNormal"" style='background:white'>"
emailBody = emailBody & "<b><u><span style='font-size:14.0pt;color:#222222'>BEAN OIL:</span></u></b><o:p></o:p></p>"
emailBody = emailBody & "<p class=""MsoNormal"" style='margin-left:.5in'>"
emailBody = emailBody & "<span style='font-size:14.0pt;font-family:Symbol;color:#222222'>·</span>"
emailBody = emailBody & "<span style='font-size:7.0pt;font-family:""Times New Roman"",serif;color:#222222'>         </span>"
emailBody = emailBody & "<b><span style='font-size:14.0pt;font-family:""Arial"",sans-serif;color:#222222'> Flat</span></b><o:p></o:p></p>"
emailBody = emailBody & "</div>"


Dim ChartTab As Chart

'Create folder for charts
On Error Resume Next
MkDir SelectedPath & "\charts\"
On Error GoTo 0
t = 1

'Copy all charts into a single tab
For Each ChartTab In Charts
ChartTab.ChartArea.Copy
WS.Select
WS.Paste
Next


'insert each chart into email body
For Each ChartObj In WS.ChartObjects
ChartObj.Chart.Export Filename:=SelectedPath & "\charts\" & PDFDate & "_Chart_" & t & ".png", Filtername:="PNG"
emailBody = emailBody & "<img src='" & SelectedPath & "\charts\" & PDFDate & "_Chart_" & t & ".png" & "'><br><br>"
t = t + 1
Next

'Print all charts into a single pdf file
If Dir(SelectedPath & "\" & "Basis Charts " & PDFDate & ".pdf") <> "" Then
Kill SelectedPath & "\" & "Basis Charts " & PDFDate & ".pdf"
End If
Sheets(Array("CIF-MASTER", "S-BE1", "S-BE2", "S-BE3", "C-BE1", "C-BE2", "C-BE3", "W-BE")).Select
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
SelectedPath & "\" & "Basis Charts " & PDFDate, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


'Compose an email
If ComposeEmail = True Then

Dim OutApp As Object
Dim OutMail As Object
Dim Signature As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
WS.Range("A1:Q" & (Position - 10)).Copy
With OutMail
.To = ""
.CC = ""
.BCC = "reports@agspreadanalytics.com"
.Subject = "Domestic Basis Charts " & PDFDate
.Attachments.Add (SelectedPath & "\" & "Basis Charts " & PDFDate & ".pdf")
End With
OutMail.Display
Signature = OutMail.HTMLBody
OutMail.HTMLBody = emailBody & Signature

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End If
WS.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
511
Micron
M
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
J
  • Solved
Email body not showing using html.
Replies
3
Views
661
Logit
Logit
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
504
josros60
J
earthworm
  • Question
on error resume next on vba
Replies
7
Views
747
Alex Blakenburg
A

Forum statistics

Threads
1,217,414
Messages
6,136,492
Members
450,016
Latest member
murarj

Share this page

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