+ Reply to Thread
Results 1 to 2 of 2

Change VBA to allow email code to bring up outlook email before sending it

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Change VBA to allow email code to bring up outlook email before sending it

    How do I modify the following code to convert the worksheet to a PDF and attach itself to an email without actually sending the email. I want the user to be able to write additional text or attach additional attachments before manually clicking on "Send" in Outlook.

    Sub CommandButton3_Click()

    Dim IsCreated As Boolean
    Dim i As Long
    Dim PdfFile As String, Title As String
    Dim OutlApp As Object

    ' Not sure for what the Title is
    Title = Range("A1")

    ' Define PDF filename
    PdfFile = ActiveWorkbook.FullName
    i = InStrRev(PdfFile, ".")
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

    ' Export activesheet as PDF
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    ' Use already open Outlook if possible
    On Error Resume Next
    Set OutlApp = GetObject(, "Outlook.Application")
    If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
    End If
    OutlApp.Visible = True
    On Error GoTo 0

    ' Prepare e-mail with PDF attachment
    With OutlApp.CreateItem(0)

    ' Prepare e-mail
    .Subject = Title
    .To = "" ' <-- Put email of the recipient here
    .Cc = "" ' <-- Put email of the recipient here
    .Body = "Please see the attached SHO." & vbLf & vbLf _

    .Attachments.Add PdfFile

    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
    MsgBox "E-mail was not sent", vbExclamation
    Else
    MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0

    End With

    ' Delete PDF file
    Kill PdfFile

    ' Quit Outlook if it was created by this code
    If IsCreated Then OutlApp.Quit

    ' Release the memory of object variable
    Set OutlApp = Nothing

    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,082

    Re: Change VBA to allow email code to bring up outlook email before sending it

    Change .Send to .Display.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Why is my email code sending a blank email?
    By gmr4evr1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-28-2015, 10:20 AM
  2. [SOLVED] Sending PDF with Outlook email
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-22-2015, 10:40 AM
  3. Button sending email code - can any change it with slight variation?
    By ld2x07 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2014, 12:13 PM
  4. [SOLVED] change font colour in outlook when sending email using vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2014, 09:55 AM
  5. [SOLVED] Reference a cell to bring in email address to vba outlook code
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2012, 09:30 AM
  6. [SOLVED] How to get rid of the warning msg from Outlook when sending email via VBA code in Excel
    By new.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2005, 05:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1