VBA code to seend seperate sheets as PDF to seperate emails

[Gomesm]

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

[gmayor]

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?

[Gomesm]

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?

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

[gmayor]

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

[Gomesm]

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.

[gmayor]

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

.to = "someone@somewhere.com"
You could change that to

If sName = "Sheet 1" then
.to = "someone@somewhere.com"
Else
.to = "someoneelse@somewheredifferent.com"
End If
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.

[Gomesm]

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

[Gomesm]

I think I got it to work, thank very much!

[Gomesm]

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?