Information from user defined field into Excel

Havic

Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
Forum,
In outlook is a user defined field with the name "ID". In that field the user can fill in his identification for a specifik e-mail by cell editing.
With VBA:

.Range("D" & EmailRow).Value = Item.Subject ' Subject

the information of subject is written into Excel. How can I get the user defined field "ID" information in the same way?

Do I need to define a variable?
If the answer is "yes", how do I realise that?

I am a novice in VBA, so google is my friend,
Hopefully has someone an advice to speeding up my knowledge

With regards
Havic
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Accessing custom fields uses a couple more lines of code and will be easier to get the value in a variable, although not 100% required. You can't just say 'item.custonfieldname' though.

Set UserProp = obj.UserProperties.Find("fieldname")
If Not UserProp Is Nothing Then
strCurrent = obj.UserProperties("fieldname").Value
End If


A sample code is at the end of this article -
 

Havic

Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
Accessing custom fields uses a couple more lines of code and will be easier to get the value in a variable, although not 100% required. You can't just say 'item.custonfieldname' though.

Set UserProp = obj.UserProperties.Find("fieldname")
If Not UserProp Is Nothing Then
strCurrent = obj.UserProperties("fieldname").Value
End If


A sample code is at the end of this article -
Diana,
Thank you for your answer!
I have tried to understand it and what I tried, it doesn't work.
The VBA below is a part of a email manager I found on the internet.
There are some Dutch sentences in it.
De line I have tried, the text is red colored

In other thing is that if de mail is forwarded the ID stays in de new incoming mail. If you do a reply it is gone.
Is there a solution for the reply mail to keep the ID?

Option Explicit
Const xlUp As Long = -4162 ' Set the enumeration for Excel's xlup Outlook kent geen xlup. De Code hiervoor is -4162 die opgeslagen wordt in een constantehmet de naam xlUp.
Private WithEvents inboxItems As Outlook.Items
Private Sub Application_Startup()
Dim outlookApp As Outlook.Application
Dim objectNS As Outlook.NameSpace
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace("MAPI")
Set inboxItems = objectNS.Folders("adress@gmail.com").Folders("Inbox").Items ' Change These Folder Names to whatever names are in your Outlook
' Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items

End Sub
Private Sub inboxItems_ItemAdd(ByVal Item As Object)

Dim Msg As Outlook.MailItem
Dim ExApp As Object
Dim ExWb As Object
Dim Attach As Attachment
Dim AttachFolder As String
Dim FileName As String
Dim EmailText As String
Dim EmailRow As Long
Dim AttachRow As Long
Dim AttachNum As Long
Dim FileNumb As Long
Dim ErrNumb As Long
Dim WkBkOpen As Boolean

If TypeName(Item) = "MailItem" Then
FileName = "C:\Mailmanager\Outlook_Email_Manager.xlsm" ' Customize this to your workbook location
If FileName = "" Then
MsgBox "De locatie van het excel bestand is niet bekend, pas de variable 'FileName' aan in de macro!"
Exit Sub
End If
' Determine If Excel File is open or not
On Error Resume Next
FileNumb = FreeFile() ' Get Free File #
Open FileName For Input Lock Read As #FileNumb ' Attempt to open the file and lock it.
Close FileNumb ' Close the file.
ErrNumb = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.


Select Case ErrNumb ' Check to see which error occurred
Case 0 ' File is NOT already open by another user.
WkBkOpen = False ' Error number for "Permission Denied"
Case 70 ' File is already opened by another user.
WkBkOpen = True
Case Else ' Another error occurred.
Error ErrNumb
End Select

' Start Excel
On Error Resume Next
Set ExApp = GetObject(, "Excel.Application") 'Set EXCEL application object
On Error GoTo 0
If ExApp Is Nothing Then
Set ExApp = CreateObject("Excel.Application")
End If
ExApp.Visible = True 'Make Excel Application Visible

If WkBkOpen = True Then 'Check If workbook is open
Set ExWb = GetObject(FileName).Application 'PAD + BESTAND
Else:
Set ExWb = ExApp.Workbooks.Open(FileName) 'PAD + BESTAND
End If

' First Available Row in Workbook
EmailRow = ExWb.Sheets("Email Db").Range("A999999").End(xlUp).Row + 1 ' (xlUp) is de constante, dit had ook (-4162) mogen zijn.
EmailText = Item.Body

With ExWb.Sheets("Email Db") ' Email DB Sheet
.Range("A" & EmailRow).Value = Item.ReceivedTime ' Email Received On
.Range("B" & EmailRow).Value = Item.SenderEmailAddress ' From Email
' .Range("C" & EmailRow).Value = Item.SenderName ' From Name
.Range("D" & EmailRow).Value = Item.Subject ' Subject
' .Range("E" & EmailRow).Value = EmailText ' Message
.Range("F" & EmailRow).Value = "Default Category" ' Default Category?"
' .Range("G" & EmailRow).Value = Item.Attachments.Count ' Count number of attachments
.Range("I" & EmailRow).Value = Item.ID ' User identification
End With

If WkBkOpen = False Then ExWb.Close (True) ' Save & Close Workbook if Previously closed
If WkBkOpen = False Then ExApp.Quit ' Quit Excel Application ir previously closed
End If
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
This should work to replace the red line don't forget to change fieldname to your field name.

Set UserProp = item.UserProperties.Find("fieldname")
If Not UserProp Is Nothing Then
.Range("I" & EmailRow).Value = item.UserProperties("fieldname").Value
End If

or this

Set UserProp = item.UserProperties.Find("fieldname")
If Not UserProp Is Nothing Then
strCurrent = item.UserProperties("fieldname").Value
End If

.Range("I" & EmailRow).Value = strCurrent

if you are properly handling errors - you could use just this
.Range("I" & EmailRow).Value = item.UserProperties("fieldname").Value
 

Havic

Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
There seems to be a problem with defining a variable.
I tried several, some gave a compile error and some "object is missing"

I have used:
Set UserProp = item.UserPro,erties.Find("ID")
If Not UserProp Is Nothing Then
.Range("I" & EmailRow).Value = item.UserProperties("ID").Value
End If


I find somewhere on the web the option:
Dim UserProp As Outlook.MailItem

The macro does not give any errors with this but the information "ID" is not added in the line in Excel
Col I​
Received OnFrom EmailFrom NameEmail SubjectEmail MessageCategory# Of Atach.ID
22-11-2020 09:18FW: 2Default Category


It is difficult to find information how to read informatie from UDF.
In my case the UDF "ID"
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
userprop is definitely not a mailitem. you can dim as just as Dim userprop, but it should work without being dimmed. The macro works if you don't get the user property?

when you used item.properties, did item capitalize, like it is in the rest of the lines:
.Range("A" & EmailRow).Value = Item.ReceivedTime ' Email Received On
 

Havic

Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
Without UDF entry, the macro works good.
Both given options lead to:

See errors below:
1606054982544.png

and
1606055231182.png

Debug
1606055262283.png
 
Similar threads
Thread starter Title Forum Replies Date
F Exchange User Information - Notes Exchange Server Administration 4
S You’ve received Free/Busy information from this user Message Using Outlook 10
C Custom Outlook Form - Populate Information from Radio Button / Check Box Using Outlook 0
S Unable to Edit Contact Information in Certain Contact Folders Using Outlook 3
I Backup email account information Using Outlook 2
T Received many email without information Using Outlook 1
R Trying to extract information between two symbols from outlook subject Using Outlook 2
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
B Clearing ALL information from Outlook 2010 Using Outlook 1
J Group email messages based upon information in InfoBar Using Outlook 0
R The changing way to access information in Office 365 New Slipstick.com Articles 0
N Outlook 2013 information message Outlook VBA and Custom Forms 1
P Outlook Macro sends information to Excel Using Outlook 1
M How to get information about a set of meetings Outlook VBA and Custom Forms 3
C Custom Forms: Question about retaining form information throughout the entire conversation Outlook VBA and Custom Forms 2
G Missing outlook 2013 information Using Outlook 2
M Outlook 2013 Calendar showing No Information Using Outlook 1
C Rule to Handle Meeting Replies that updates tracking information Using Outlook 2
T Contacting <Server Name and Path> for Information When Adding Attachments Using Outlook 0
E Autofill Information in Email Template Using Outlook 2
G Hidden information Exchange Server Administration 0
J Header information missing in one account only Using Outlook 10
I Search email with information in detailed headers Using Outlook 3
G Discrepancy in drafts information in multiple computers Exchange Server Administration 0
G Pop up information location issue Using Outlook 4
W How to build a macro that interacts with and uses information from an Excel sh Using Outlook 1
J Problems starting Information Store Server in Exchange 2003 Exchange Server Administration 13
J Issues printing HTML emails - Contacting server for information Using Outlook 1
M Outllook 2000 - 2 Errors - The Information Service / The Messaging Interface Using Outlook 7
I Is there a way to transfer Windows Address Book files to Outlook? (Contact information FROM Windows Address Book TO Outlook?) Using Outlook 1
S Accessing source information Using Outlook 1
T Copied Contact Does Not Update With Active Directory Information Changes Immediately. How Do I Force Update? Using Outlook 1
R Error message "Cannot save free/busy information" when changing or deleting appointments in Outlook. Using Outlook 4
C 0x800CC0D "Can not find the email server. Verify the server information in your account properties" Using Outlook 4
N Trouble importing calender information from iPhone into OutLook 2010 Using Outlook 1
L Drop down menu box in Office 2003 are too small. I cannot see all the information to fill out serve Using Outlook 2
Z Outlook Crashing When Editing Contact Information in Contacts Using Outlook 2
K "The information service is not installed in your computer" message and Outlook 2010 not opening Using Outlook 2
D Presence information goes away in Outlook 2010 after undocking laptop. Using Outlook 2
K Outlook: Can not save free / busy information error message Using Outlook 2
R Cannot start Exchange Information Store Exchange Server Administration 3
T "Look up Outlook Contact" from a received email does not display all of the contact information Using Outlook 9
K Creating a invite that publishes different location information for organizer vs people on the inviteed list Using Outlook 1
S Error updating public folder with free/busy information on virtual machine server. The error number Using Outlook 2
A Exchange 2010 DB - Unable to communicate with the Information Store to coordinate log truncation Exchange Server Administration 0
S FreeBusy information not shown for few users Exchange Server Administration 14
K Moving to new PC-old auto complete information Using Outlook 5
M trying to print emails but get Micorsoft Outlook contacting server for information Using Outlook 6
S outlook.pst is not compatible with this version of the personal folders information service Using Outlook 8
P Cannot open your default e-mail folders. The information store could not be opened. Using Outlook 7

Similar threads

Top