after olCreatedItem.display, how can I tell if user has sent the email?

My vba subroutine preformats an email, and then displays the email so the user can modify it. When the user sends the message, I want to log the actual To address.  The attached code comes close, but it logs the preformatted To address.  

I know I could create my own form that duplicates outlook's email form, but that is not a very clean approach.

I could also search through the Sent Items folders to see if the message was there, but I am worried the folder might not be sorted by date sequence. Plus, the user might have change the subject etc, so the search would not be reliable.

I hope someone can come up with a simpler solution.


Sub sendEmail(strContactEmail, strCc, strEmailBody, strEmailSubject, Optional strEmailAttachments)
Dim olLook, olNewEmail As MailItem
Dim strContactFirstName

Set olNewEmail = olapp1.CreateItem(0)

   With olNewEmail   'Attach template
      .To = strContactEmail
      .CC = strCc
      .Body = strEmailBody
      .Subject = strEmailSubject
      .attachments.Add (strEmailAttachments)
      
      .Display (True) ' make it modal and wait for user action
   End With

On Error Resume Next
Dim wasSent As Boolean
wasSent = olNewEmail.Sent
If Err <> 0 Then wasSent = True
On Error GoTo 0
If wasSent Then
    MsgBox "Sent to" & strContactEmail
Else
    MsgBox "Email was canceled and will not be logged"
End If
End Sub

Open in new window

LVL 5
rberkeConsultantAsked:
Who is Participating?
 
TextReportConnect With a Mentor Commented:
This could be a version issue, it works perfectly as posted with Outlook 2007 and Access 2007. I will check with 2003 in the next couple of days.
Cheers, Andrew
0
 
TextReportCommented:
As you are already using the Display with the Modal option then you should be able to use the .Sent property of the MailItem.
Cheers, Andrew
   With olNewEmail   'Attach template 
      .To = strContactEmail 
      .CC = strCc 
      .Body = strEmailBody 
      .Subject = strEmailSubject 
      .attachments.Add (strEmailAttachments) 
       
      .Display (True) ' make it modal and wait for user action 
      If .Sent Then
          MsgBox "Sent to" & strContactEmail 
      Else 
          MsgBox "Email was canceled and will not be logged" 
      End If 
   End With 

Open in new window

0
 
David LeeCommented:
Hi, rberke.

If you want to record the addresses of all sent item, then the simplest approach is to trap the Application object's ItemSend event.  It fires each time an item is sent.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
rberkeConsultantAuthor Commented:
bluedevilfan

I thought your idea would be simple, but I can't get it to work.

Thisoutlooksession module has
   Public LastItemSubject As String
   Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
   Stop
    LastItemSubject = Item.Subject
   End Sub

The event does not fire when the user sends an email performating by my vba SendEmail routine.
the event fires properly when an email is sent manually.  

my subroutine doesn't  say application.enableevents = false    but that is the way things are acting.

0
 
rberkeConsultantAuthor Commented:
Textreport: Your code will not work. When the user clicks Send on the modal form, the olNewEmail variable pointing to the email gets deallocated, so olNewEmail.sent aborts.  

My originally posted code handles that condition to avoid the abort, but that does not solve the problem.
0
 
rberkeConsultantAuthor Commented:
Andrew, Yes, I am using Outlook 2003, so it could be a version issue.  Frankly, I was surprised to find olNewEmail.Sent did not work.  I will be interested in your results when you check 2003.  

By the way, I just retested with very simple code.  I was wrong when I said the olNewEmail variable was deallocated.  It just points to a nonexistent email, so msgbox olNewEmail.sent gets the following runtime error

Run-time error '-1594621686 (a0f4010a)':
The item has been moved or deleted.
0
 
David LeeCommented:
rberke,

How are you sending?  I don't see that operation in the code you posted.
0
 
rberkeConsultantAuthor Commented:
here is the exact code


Option Explicit

Const olMsg = 3
Dim xlapp1
Dim olapp1
Sub test()
Set olapp1 = Application
Call sendEmail("rberke@xxxxxxx.com", "", "test", "test")
End Sub


Sub sendEmail(strContactEmail, strCc, strEmailBody, strEmailSubject, Optional strEmailAttachments)
Dim olLook, olNewEmail As MailItem, dupEmail
Dim strContactFirstName

Set olNewEmail = olapp1.CreateItem(0)

Set dupEmail = olNewEmail
   With olNewEmail   'Attach template
      .To = strContactEmail
      .CC = strCc
      .Body = strEmailBody
      .Subject = strEmailSubject
      .Display (True) ' make it modal and wait for user action
   End With
MsgBox olNewEmail.Subject

End Sub

Open in new window

0
 
David LeeCommented:
I don't see any command in the code that is sending.  The message has to be sent for the ItemSend event to fire.
0
 
TextReportConnect With a Mentor Commented:
Hi BlueDevilFan the send is happening when the user clicks the send button following the displayof the email.
Hi rberke, I suspect you will need to use the EntryID (I think that is the right name) and then find the message again.
Cheers, Andrew
0
 
David LeeCommented:
Hi, Andrew.  

I asked the question based on what rberke said in this post, "The event does not fire when the user sends an email performating by my vba SendEmail routine.  the event fires properly when an email is sent manually."  If the item is being sent when the user clicks the Send button, then that's a manual send.  The outcome seems to contradict the author's statement that the event fires when sending manually, which this would be, but does not fire when send via his code.  That sounded to me like he was sending via code that was not in the sample he posted.  
0
 
rberkeConsultantAuthor Commented:
Andrew, I am sorry my phrase  "sending an email manually" mislead you. The user most ALWAYS manually click "Send" button even when vba does the pre-formatting. So, I will call this a "semi automated" approach.

And, to be even more precise,  This semi-automated approach sometimes causes the SentItem event to fire, and sometimes does not, depending on the .display  modal:= setting.

A .display modal:=false, followed by an Exit Sub, the even DOES fire, but that does not meet my needs because my routine does not get an opputunity to write to the log file.

A .display modal:=false, followed by a Msgbox "please click send before proceeding" has a different problem.  When the user clicks Send, they get an error message
      "A dialog box is open in Outlook. Close the dialog box, and then try again."

So, I think I really need to say modal:= true.

TextReports idea of EntryId might work.   I think I would have to loop through the sent items mailbox looking for the correct entryid.  This would be a reliable way of searching because it would not be influenced by changes in the subject.

But, it would probably be very slow especially if the sent items mailbox was not sorted by date sequenece.

Does anybody know of a way to do a "direct read" using EntryId as a primary key instead of looping?

0
 
David LeeCommented:
Do you just need to record the addresses that each item is sent to or do you need to record the message subject, or other information, and the addresses?
0
 
rberkeConsultantAuthor Commented:
Initially it will be addressees, subject and attachment names.  Other information may be needed in the future.
0
 
David LeeCommented:
And will it be for every item sent or just those that are sent via your routine?
0
 
rberkeConsultantAuthor Commented:
Just those sent by my routine.  
0
 
rberkeConsultantAuthor Commented:
By the way, I never mentioned one important part of the user interface.  
The user calls my vba by selecting maybe a dozen emails in his inbox, then running my routine.

My routine then preformats and displays replies to the first selected email.  Every time the user clicks Send, the routine goes to the next selected email.

It is this loop requirement that is making this sooooo difficult.  I don't know how to continue to the next item after sending the current item.
0
 
rberkeConsultantAuthor Commented:
Actually, I think I could get this to work in a VERY ugly manner with something like the attached code.  

But, this is too ugly, and I don't think I will do it. For instance, if there were 12 entries in the selection, Line100: would be called  12! (12*11*...*3*2*1) times.
Then,
       sub sendEmail()
           ...... previous code unchanged .......
           olNewEmail.Display modal:=false
           EntryId_olNewEmail = olNewEmail.entryid
       end sub

and, in thisoutlooksession
   Public EntryId_olNewEmail As long
   Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
     if EntryId_olNewEmail <> Item.EntryId then exit sub
     call writelog (Item.subject, etc)
     call sendNEXTEmail()
   End Sub




   Then ItemSend event code would call sendNextEmail after each

 
Dim olNewEmailEntryId as long

sub sendNEXTEmail()
     For Each Item In olapp1.ActiveExplorer.Selection
line100:	 if  instr(allreadyprocessed, prevEmail.entryid) =0 then
	           alreadyprocessed = alreadyprocessed & "," & item.entryid
		Set ThisItem = item
		goto sendit
	End if
    Next item 
    exit sub  ' all previous emails have been sent
sendit:
    if "y" <> inputbox ("do you want to process the next selected email",,"y")) then 
        exit sub
   end if
   Call sendEmail(item.from, "", strEmailBody:="test", strEmailSubject:=item.subject)
  end sub

Open in new window

0
 
David LeeConnect With a Mentor Commented:
Instead of keying on EntryID you could set a property and key on it.  Something like

    Set olkProp = Item.UserProperties.Add("MyFlag", olYesNo)
    olkProp.Value = True

Then in the ItemSend event you can test to see if the user property exists and is set to True.  You also need to modify the loop you're using to process the selected items.  If you use a FOR EACH loop the code will skip every other item.  That happens because each time an item is sent the items all drop one position.  To avoid this the code must use a counter and count backwards.  Something like

    For intIndex = olapp1.ActiveExplorer.Selection.Count to 1 Step -1
        Set Item = olapp1.ActiveExplorer.Selection.Item(intIndex)
        'Remaining code'
    Next
0
 
rberkeConsultantAuthor Commented:
I think your use of userproperties is important because EntryId is always blank until after the SendItem event has completed.

So, that portion could be made to work.

But, the real thing that does not work is the call to  sendNEXTEmail .

the ItemSend event fires BEFORE the Outlook Email form has closed.

Consequently, the call to sendNEXTEmail  cannot open up another email dialog.

I am giving up and closing the problem. This is getting way to complicated.  

I am just going to tell the users that the log will always reflect the Preformatted values, not the actual values.  They will have to accept something that is less than perfect.
0
 
rberkeConsultantAuthor Commented:
"A" for effort, but we never got a solution, and I can't spend anymore time trying
0
 
rberkeConsultantAuthor Commented:
I have thought of another approach. Every form will be non-modal, so I think this approach might work. I may never have time to implement this but I thought I would document it for future researchers.

 
* user selects multiple inbox messages then uses menu button to call ReplyToFirstItem
* The user would then be given a non-modal pre-formatted outlook reply to the first inbox selection.
* The user would change the reply as desired and press send.
* the thisoutlooksesion application_SendItem routine would
    1 Turn inboxItem Flag Status to RED
    2 writelog with the ACTUAL subject, to addressees etc.

*  The user would then see a non-modal form with a
     Button1.caption:
         "you have finished processing inbox item 1 out of 5.
          Press this button to process next inbox item"
 
    Button1_Click:  
          call ReplyToRemainingItems
     
     Button2.caption
          "press this button to quit"

thanks for your help,

rberke

------- a few more details need to be fleshed out ------------------------------
* there would be module level variables. They would probably be declared in thisoutlooksession
    public TotalInboxItemsToProcess as long
    public idxInboxItemBeingProcessed as long

* sub FirstItemReply would contain
    if selection.count > 1 then
         TotalInboxItemsToProcess  = loop through all items in inbox selection
                                     counting the items which dd NOT have a RED status flag.
         MasterForm.Display modal:=false.
         idxInboxItemBeingProcessed  = the first inbox item which was not RED
    end if
    ....
    call ReplyToInboxItem(idxInboxItemBeingProcessed)

* sub ReplyToRemainingItems
    for idxInboxItemsBeingProcessed  _
              = idxInboxItemBeingProcessed + 1 to selection.count
       if selection(idxInboxItemBeingProcessed) is NOT flagged as RED
          goto processit
      end if
    next
    end sub
processit:
      call ReplyToInboxItem(idxInboxItemBeingProcessed)

* sub  ReplyToInboxItem(idx)
    Set olkProp = Item.UserProperties.Add("MyFlag", oltext)
    olkProp.Value = "FromInbox"&idx
    .... format olNewEmail  
     olNewEmail.display modal:=false
     end sub

* thisoutlook
   Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
     if item.userproperties("MyFlag") <> "FromInbox"& idxInboxItemBeingProcessed) then
         exit sub
     end if
     set flag status RED for inbox selection(idxInboxItemBeingProcessed))
     call writelog (Item.subject, etc)
   End Sub



   
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.