Consulting

Results 1 to 9 of 9

Thread: VBA code to seend seperate sheets as PDF to seperate emails

  1. #1
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location

    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

  2. #2
    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

  3. #3
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    Quote Originally Posted by gmayor View Post
    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

  4. #4
    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

  5. #5
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    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.

  6. #6
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    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

  8. #8
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    I think I got it to work, thank very much!

  9. #9
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •