CODEWITHSUNDEEP
excelvbaoutlook
user10236952
user10236952

Reputation: 39

mailitem.entryID in Excel VBA

Can I use mailitem.entryID in Excel VBA?

I have a tool using excel where I can send an outlook email to recipients using spreadsheet as the UI to display user data. I need to store the entryID of each of the emails send to the user in the excel table. Can I set in the code (excel vba) mailitem.entryID = worksheet.cells().value ? Will it retrieve the entryID? Can you give me your input regarding this? Thank you for your help. 

Dim AppOutlook As Object
Dim MailOutlook As Object
Dim Emailto, ccto, sendfrom As String

Set AppOutlook = CreateObject("Outlook.Application")
Set MailOutlook =AppOutlook.CreateItem(0)
Emailto = worksheet.Cells().Value
ccto = worksheet.Cells().Value
sendfrom = "email"

  With OutMail
    .SentOnBehalfOfName = sendfrom
    .To = Emailto
    .CC = ccto
    .BCC = ""
    .Subject = 
    .BodyFormat = olFormatHTML
    .HTMLBody = "body here"
    .Send

This is my code, and I plan to add the code worksheet.cells.value = MailOutlook.entryID at the last line of the code. Is it possible? and where to add the AddItem event?

Upvotes: 0

Views: 1631

Answers (3)

Seiya Su
Seiya Su

Reputation: 1874

The mail item may not exist any longer after calling the Send method. It can be moved to the Outbox folder for further processing by the transport provide. Item can be marked for processing by the transport provider, not being yet sent. So, we need to handle the ItemSend event in the code.

If you need to be sure that the mail item was sent for sure I'd recommend handling the ItemAdd event of the Items class (see the corresponding property of the Folder class). For example, when an Outlook item is sent, a sent copy is placed to the Sent Items folder in Outlook. You may handle the ItemAdd event for that folder to be sure that the item was sent for sure. Consider adding a user property before displaying the Outlook item and checking it in the ItemAdd event handler to identify the item uniquely.

Demo code based on your code:

Sub Test3()
Dim AppOutlook As Object
Dim MailOutlook As Object
Dim Emailto, ccto, sendfrom As String

Set AppOutlook = CreateObject("Outlook.Application")
Set MailOutlook = AppOutlook.CreateItem(0)

Emailto = Worksheets("Sheet3").Cells(1, 1).Value
ccto = Worksheets("Sheet3").Cells(2, 1).Value
sendfrom = "[email protected]"

With MailOutlook
    .SentOnBehalfOfName = sendfrom
    .To = Emailto
    .CC = ccto
    .BCC = ""
    .Subject = "Test"
    .BodyFormat = olFormatHTML
    .HTMLBody = "body here"
    '.Display
    .Send

End With
End Sub

Some ItemAdd snippet for you reference(The current event is not the right one, we still need to test it):

Option Explicit
Private objNS As Outlook.NameSpace
Private WithEvents objItems As Outlook.Items

‘Private Sub Application_Startup()
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim objWatchFolder As Outlook.Folder
Dim AppOutlook As Object
Set AppOutlook = CreateObject("Outlook.Application")

Set objNS = AppOutlook.GetNamespace("MAPI")

'Set the folder and items to watch:
Set objWatchFolder = objNS.GetDefaultFolder(olFolderInbox)
Set objItems = objWatchFolder.Items

Set objWatchFolder = Nothing
End Sub

Private Sub objItems_ItemAdd(ByVal Item As Object)

' Your code goes here 
MsgBox "Message subject: " & Item.Subject & vbcrlf & "Message sender: " & Item.SenderName &" (" & Item.SenderEmailAddress & ")"
Worksheets("Sheet3").Cells(3, 1).Value = Item.EntryID
Set Item = Nothing
End Sub

Upvotes: 1

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66266

You can read the EntryID property after the message is sent. You cannot do that before or immediately after sending the message - it will be changed when the message is asynchronously sent and moved to the Sent Item folder. The erliest you can access the entry id in the Sent Items folder is when the Items.ItemAdd event fires in the Sent Items folder.

Upvotes: 1

girlvsdata
girlvsdata

Reputation: 1644

The MailItem object is part of Outlook's VBA Object library. You can see the documentation for the MailItem object on MSDN here.

To use VBA objects from a different program in Microsoft Office (eg. calling Outlook from Excel, calling Visio from Word, calling Excel from Powerpoint) you first need to make sure you have the right References selected in your Visual Basic Editor (VBE).

How to turn on Outlook references in Excel:

  • In Excel's VBE, go to Tools > References.

  • A References - VBAProject box will appear.

  • Under Available References: scroll down until you reach something like Microsoft Outlook 16.0 Object Library (This will differ depending on the version of Office you are using)

  • Tick the box and press OK.

Now the Outlook Object references have been enabled, you should be able to call Outlook objects and methods from Excel, including MailItem.

Upvotes: 0

Related Questions