?
Solved

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

Posted on 2009-12-27
22
Medium Priority
?
439 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:rberke
  • 12
  • 7
  • 3
22 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 26128599
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
 
LVL 76

Expert Comment

by:David Lee
ID: 26131521
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
 
LVL 5

Author Comment

by:rberke
ID: 26134476
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 5

Author Comment

by:rberke
ID: 26134578
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
 
LVL 28

Accepted Solution

by:
TextReport earned 1080 total points
ID: 26134706
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
 
LVL 5

Author Comment

by:rberke
ID: 26134775
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
 
LVL 76

Expert Comment

by:David Lee
ID: 26135372
rberke,

How are you sending?  I don't see that operation in the code you posted.
0
 
LVL 5

Author Comment

by:rberke
ID: 26135712
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
 
LVL 76

Expert Comment

by:David Lee
ID: 26136715
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
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 1080 total points
ID: 26136808
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
 
LVL 76

Expert Comment

by:David Lee
ID: 26136837
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
 
LVL 5

Author Comment

by:rberke
ID: 26139036
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
 
LVL 76

Expert Comment

by:David Lee
ID: 26139648
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
 
LVL 5

Author Comment

by:rberke
ID: 26140487
Initially it will be addressees, subject and attachment names.  Other information may be needed in the future.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 26140633
And will it be for every item sent or just those that are sent via your routine?
0
 
LVL 5

Author Comment

by:rberke
ID: 26141627
Just those sent by my routine.  
0
 
LVL 5

Author Comment

by:rberke
ID: 26141777
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
 
LVL 5

Author Comment

by:rberke
ID: 26141925
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
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 920 total points
ID: 26142765
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
 
LVL 5

Author Comment

by:rberke
ID: 26143270
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
 
LVL 5

Author Closing Comment

by:rberke
ID: 31671077
"A" for effort, but we never got a solution, and I can't spend anymore time trying
0
 
LVL 5

Author Comment

by:rberke
ID: 26144868
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question