Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    13

    Unanswered: Excel 2007 VBA Check whether Outlook email was sent

    Hi there,

    I use Excel 2007 and Outlook 2007.

    I stole a bit of code from the internet and edited it for my purposes. It opens up a new mail window and automatically populates the To field. It also populates the Subject and Body with information from the workbook, and gives the user an error if information is missing.

    However, it allows the user to edit the email before sending it out in case something needs to be changed in the subject or the information. This inherently allows the user to discard the email and prevent it from being sent even after the subroutine has run.

    For continuity purposes, I would like to be able to track whether an email with this information has been sent. Here is my question:

    Is there a way for VBA to detect whether the email message it initiated was sent or discarded?

    Right now, I have a message box appear that merely asks the user whether they sent the email, but I would like this piece to be automated if it's not too troublesome.

    Here is the code I have right now:
    Code:
    Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long
    Sub FMASendEMail()
        Dim Email As String, Subj As String
        Dim Msg As String, URL As String
        
        Sheets("FMA").Activate
        
        If Range("C3").Value = "Must enter on Info tab" Or _
            Range("C5").Value = "Must enter on Info tab" Or _
            Range("C7").Value = "Must enter on Info tab" Or _
            Range("C9").Value = "Must enter on Info tab" Or _
            Range("C11").Value = "" Or _
            Range("C13").Value = "" Then
            MsgBox "You must fill all required information"
            Exit Sub
        End If
        If Range("E3").Value = 0 Then
            MsgBox "You must enter your name on the Info tab"
            Exit Sub
        End If
    
    '       Get the email address
            Email = "jacobmango@xerox.com"
            
    '       Message subject pulls customer name
            Subj = Range("C3").Value & " FM Audit Information"
    
    '       Compose the message
            Msg = "Jacob," & vbCrLf & vbCrLf _
                & "Please see the FMA information for " & Range("C3").Value & " below." & vbCrLf & vbCrLf _
                & "Company Name: " & Range("C3").Value & vbCrLf _
                & "Contact Name: " & Range("C5").Value & vbCrLf _
                & "Contact Phone: " & Range("C7").Value & vbCrLf _
                & "Contact Email: " & Range("C9").Value & vbCrLf _
                & "Approx number of devices to find: " & Range("C11").Value & vbCrLf _
                & "New contract or print study: " & Range("C13").Value & vbCrLf & vbCrLf _
                & "Please let me know if you have any questions." & vbCrLf _
                & "Thank you," & vbCrLf & vbCrLf _
                & Range("E3").Value
            
    '       Replace spaces with %20 (hex)
            Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
            Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                    
    '       Replace carriage returns with %0D%0A (hex)
            Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
    '       Create the URL
            URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
    
    '       Execute the URL (start the email client)
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormal
    
            If MsgBox("Did you send the email?", vbYesNo, "Email Sent?") = vbYes Then
                Range("C18").Value = "Email sent!"
            End If
    Any suggestions or concerns would be appreciated.
    Thank you so much.

    -Jacob

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    There are a number of ways of sending emails; if you were willing to rewrite the distribution bit you could use the one that I use, in which you can designate whether you want the email to display first, or to just send automatically.


    Code:
    Dim OutApp As Object
    Dim OutMail As Object
    
       Set OutApp = CreateObject("Outlook.Application") 
       Set OutMail = oApp.CreateItem(0) 
          With OutMail 
             .To = ""
             .CC = ""
             .BCC = ""
             .Subject = ""
             .Body = ""
             .BodyFormat = 3
                '1=Plain text, 2=HTML 3=RichText
             .Send 
                'or use .Display 
             '.Attachments.Add WBook.FullName
          End With 
    
       Set OutMail = Nothing 
       Set OutApp = Nothing

  3. #3
    Join Date
    Jan 2012
    Posts
    13
    Christyxo,

    I'm sorry if there was some ambiguity to my problem. I already have the email to display first, leaving the user the option to edit it before they send, or to not send it at all.

    My problem is that I do not know of a way to systematically track whether or not the user sent the email that was displayed.

    As a side note, I appreciate that your code is simpler than the code I am using, however. When I try to run this line:
    Code:
    Set OutMail = oApp.CReateItem(0)
    It returns a Run-time error '424': Object required.

    Do you know why this would not work for me?

    Thanks.

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry, the mistake in that is my own typing error. The line should have read

    Code:
    Set OutMail = OutApp.CReateItem(0)
    Another issue that I spotted today is that if you are using this to send attachements, move the attachment line to the top of the With... (I deleted the file after sending but it was deleting it before Outlook had a chance to attach it).

    Code:
          With OutMail 
             '.Attachments.Add WBook.FullName
             .To = ""
             .CC = ""
             .BCC = ""
             .Subject = ""
             .Body = ""
             .BodyFormat = 3
                '1=Plain text, 2=HTML 3=RichText
             .Send 
                'or use .Display 
          End With
    but to get back to your problem, I'm afraid that I have a similar problem to you that I don't know how to cancel the email if I decide I don't want it.

    What I have done instead is to create a message box which would run before the email bits. It asks if it's ok to send the message. Yes means it sends. No means you edit it before sending. Cancel will stop the message.

    I'll give you my method because I don't entirely understand yours on a Friday Afternoon but I'll have another look on the weekend.

    Code:
    Select Case MsgBox("Is the following message ok to be sent? Select no, to edit): ", vbYesNoCancel)
    
    Case vbYes
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Attachments.Add Filename
            .ReadReceiptRequested = True
            .BodyFormat = 1 '1=Plain text, 2=HTML 3=RichText
            .Body = StrBody
            .Subject = StrSubject
            .To = StrMailTo
            .CC = ""
            .BCC = ""
            .Send   'or use .Display
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    Case vbNo
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .Attachments.Add Filename
            .ReadReceiptRequested = True
            .BodyFormat = 1 '1=Plain text, 2=HTML 3=RichText
            .Body = StrBody
            .Subject = StrSubject
            .To = StrMailTo
            .CC = ""
            .BCC = ""
            .Display   'or use .Send
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
            
    Case Else
    
        'Do Nothing
    
    End Select

Tags for this Thread

Posting Permissions

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