VBA code to seend seperate sheets as PDF to seperate emails

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?

Quote:

---

Originally Posted by 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?

---

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.

Code:

---

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

---

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

Code:

---

.to = "someone@somewhere.com"

---

You could change that to

Code:

---

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.

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?