Hi
I need help with a code to send at touch of a button 2 separate emails, each with separate sheet as a PDF. The emails need o have its own subject and content.
Thanks
Hi
I need help with a code to send at touch of a button 2 separate emails, each with separate sheet as a PDF. The emails need o have its own subject and content.
Thanks
Are these 'separate sheets' in the same workbook? Are they the only sheets in the workbook? What are the sheet names? Are the e-mail recipient details in the workbook? If so where? If not, where do they come from? Where does the subject come from?
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
Hi theseare seperate sheets of the same workbook. There is two of them but that I need to be se t, but there are other sheets on the workbook. The emails are not on the workbook, they could be just part of the code directly. Sheets lets call them Sheet 1 and Sheet 2. The subject could also bepart of the workbook but can be retrieve from the sheets.
thanks
VBA doesn't do guesswork, however you can modify the following to give you the results you need. Note that it requires the function from 'http://www.rondebruin.nl/win/s1/outlook/openclose.htm to start Outlook properly.
Option Explicit Sub SendSheetsAsPDF() Dim olApp As Object Dim olMail As Object Dim xlBook As Workbook Dim xlSheet As Worksheet Dim sSheet As String Dim sPath As String Dim sName As String Dim olInsp As Object Dim wdDoc As Object Dim oRng As Object If ActiveWorkbook.path = "" Then MsgBox "Save the workbook first" Exit Sub End If On Error Resume Next Set xlBook = ActiveWorkbook sPath = xlBook.path & Chr(92) Set olApp = OutlookApp() For Each xlSheet In xlBook.Sheets sName = xlSheet.Name If sName = "Sheet 1" Or sName = "Sheet 2" Then 'the names of the sheets to process xlSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=sPath & sName & ".pdf" Set olMail = olApp.createitem(0) With olMail .BodyFormat = 2 'html Set olInsp = .GetInspector Set wdDoc = olInsp.WordEditor 'access the message body for editing Set oRng = wdDoc.Range oRng.collapse 1 oRng.Text = "This is the body text of the message" & vbCr & "This is another line of text etc" .to = "someone@somewhere.com" .Subject = "Please find workbook attached" .display .attachments.Add sPath & sName & ".pdf" End With End If Set olInsp = Nothing Set wdDoc = Nothing Set oRng = Nothing Next xlSheet lbl_Exit: Set olApp = Nothing Set olMail = Nothing Set xlBook = Nothing Set xlSheet = Nothing Exit Sub End Sub
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
Does this code sends two separate emails to two separate email addresses? with the PDF's attached? I am only trying to this because currently on my sheet I have two buttons each sending an email with a PDF of its respective sheet. The problem i9s that one of them when activated deletes one of the activex textboxes of the sheet before sending. hence I thought I could just get a button t send both emails in one VBA code.
D you follow my reasoning.
It sends two separate e-mails, one with each named sheet, but you will have to tell the macro which addresses to send those message to. Currently it sends both to
You could change that to.to = "someone@somewhere.com"
Note that you will again have to put the correct sheet name in place of 'Sheet 1' and the macro makes no allowance for illegal filename characters in the real sheet names.If sName = "Sheet 1" then .to = "someone@somewhere.com" Else .to = "someoneelse@somewheredifferent.com" End If
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
Sorry gmayor, appreciated the help, when you say Rondbruin function what do you mean? I am pretty new at this? Can you let me have the whole code or explain what to do? Thanks
I think I got it to work, thank very much!
One more question, using the same sort of code how can I send a group of sheets in one pdf document to one single email?