0

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?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user10236952
  • 29
  • 1
  • 10

3 Answers3

1

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.

Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78
1

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 = "test@outlook.com"

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
Seiya Su
  • 1,836
  • 1
  • 7
  • 10
  • Hi, thank you for your input. But will the addItem be using outlook vba or excel vba? Because, the event has to be placed in the sent item right. – user10236952 Aug 21 '18 at 03:52
  • I trying to add the event to Excel VBA, so we can try set the Item.EntryID to Range. But the best place is to handle the event in Outlook VBA – Seiya Su Aug 21 '18 at 03:55
  • https://stackoverflow.com/questions/23746344/excel-vba-code-to-retrieve-e-mails-from-outlook， we can use this in Excel VBA. After send the mail, use the subject to get the latest mail item, and then get EntryID. – Seiya Su Aug 21 '18 at 06:48
  • Hi, thank you for your help. But, due to the needs to moved the email from sent item to another folder, I've decided to retrieve the ID, separately. Retrieving will only be done only when I moved the sent items to another personal folder. – user10236952 Aug 27 '18 at 03:43
  • If we done as your said: "retrieving will only be done only when I moved the sent items to another personal folder", the operation will be easy to implement – Seiya Su Aug 27 '18 at 04:21
  • yes, in fact, I already retrieved all the ID of the sent item in my personal folder. thank you again for your input :) – user10236952 Aug 27 '18 at 05:15
0

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.

girlvsdata
  • 1,596
  • 11
  • 21