UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Capturing Sender And Recipients When Sending Email, Any Version    
 
   
accessDatabase
post Nov 20 2017, 09:11 AM
Post#1



Posts: 50
Joined: 17-December 15



Hello all,

I am trying to add a new button to the ribbon when sending a new email. In addition to sending the email as per normal it will capture the sender's email address, the recipients, subject, date/time and body of the email and insert these as a new record in an Access database. I can't find how to reference some of these in the mailItem object for an email which hasn't been sent yet, or how to access the close/unload event, if they can be accessed from there. I can get the body and subject of the email by using mailItem.body and mailItem.subject but mailItem.sender, recipients and sentOn (understandably with this one) are empty.

How do I access these, is it through an event, and if so, how?!

Thanks!
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 10:47 AM
Post#2


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



I think you'd want to look into using the Application_ItemSend event

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 11:34 AM
Post#3


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



Something like:

CODE
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
10        On Error GoTo Error_Handler
          Dim recips                As Outlook.Recipients
          Dim recip                 As Outlook.Recipient
          Dim pa                    As Outlook.PropertyAccessor
          Const PR_SMTP_ADDRESS     As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"

20        With Item
              'Subject
              '---------------------
30            Debug.Print .Subject
              'Email Body
              '---------------------
              Debug.Print .Body
              'Debug.Print .HTMLBody
              'Sender's Info
              '---------------------
40            Debug.Print .SenderEmailAddress
              'Recipients' Info
              '---------------------
50            Set recips = .Recipients
60            For Each recip In recips
70                Set pa = recip.PropertyAccessor
80                Debug.Print recip.Name & " " & pa.GetProperty(PR_SMTP_ADDRESS)
90            Next
100       End With

Error_Handler_Exit:
110       On Error Resume Next
          'If Not MyObjName Is Nothing Then Set MyObjName = Nothing
120       Exit Sub

Error_Handler:
          'LogError Err.Number, Err.Description, sModName & "\Application_ItemSend", , True, Erl
130       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Application_ItemSend" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
140       Resume Error_Handler_Exit
End Sub

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
accessDatabase
post Nov 21 2017, 10:02 AM
Post#4



Posts: 50
Joined: 17-December 15



Hi Daniel,

Thanks for your reply and the code. I looked up Application Item Send event but couldn't see how to activate it.

I copied your code into the 'This Outlook Session' module, was that the best place to put it?
Go to the top of the page
 
DanielPineault
post Nov 21 2017, 10:31 AM
Post#5


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



Yes.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
accessDatabase
post Nov 21 2017, 11:10 AM
Post#6



Posts: 50
Joined: 17-December 15



Hi Daniel,
Thanks for that, really appreciate your help.
Can I ask one last thing? The .SenderEmailAddress wasn't pulling anything through. Would you expect that to be populated?
I'm using Outlook 2010 with an Exchange Account.
Go to the top of the page
 
DanielPineault
post Nov 21 2017, 11:14 AM
Post#7


UtterAccess VIP
Posts: 5,450
Joined: 30-June 11



Normally, I'd say yes? iconfused.gif

I think this has to do with using Exchange vs everything else. If you're using Exchange then you need to use the PropertyAccessor to get the information.



In such a case, I think you need to first check to see if the account is using Exchange
CODE
Dim recips As Outlook.Recipients
Dim recip As Outlook.Recipient
Dim pa As Outlook.PropertyAccessor
Const PR_SENDER_ADDRTYPE_W As String = "http://schemas.microsoft.com/mapi/proptag/0x0C1E001F"

If item.SenderEmailType = "EX" Then
    recips = mailItem.Recipients
    For Each recip In recips
        pa = recip.PropertyAccessor
        Debug.Print "Sender's Email: " & pa.GetProperty(PR_SENDER_ADDRTYPE_W)
    Next
Else
    Debug.Print "Sender's Name: " & Item.sender
    Debug.Print "Sender's Email: " & Item.SenderEmailAddress
End if


The above is simply untested aircode, I don't currently have access to an Exchange server account to test.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
accessDatabase
post Nov 21 2017, 12:39 PM
Post#8



Posts: 50
Joined: 17-December 15



Hi Daniel,

Just tested that code on an Exchange email account. It didn't quite worked how we hoped!

The Item.SenderEmailType and Item.SenderEmailAddress were empty.
Item.Sender gives Run Time error 91 - Object Variable or With Variable not set.
pa.GetProperty(PR_SENDER_ADDRTYPE_W) gives Error -2147221246 Object does not support this property "http://schemas.microsoft.com/mapi/proptag/0x0C1E001F"
Go to the top of the page
 
accessDatabase
post Nov 24 2017, 05:31 AM
Post#9



Posts: 50
Joined: 17-December 15



I think I have managed to get the fields that I need by combining those a bit and by using Outlook 2016. I was using Outlook 2010 before and the fields were different!

I want to just fire the code that takes those fields and puts them into an Access database when the user uses a custom button on the ribbon rather than the standard send button. The custom button would also send the email. How would I access the application_ItemSend event in that scenario?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 04:39 PM