Information from user defined field into Excel

Status
Not open for further replies.

Havic

New 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
 
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 -
 
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
 
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
 
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"
 
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
 
Without UDF entry, the macro works good.
Both given options lead to:

See errors below:
1606054982544.png

and
1606055231182.png

Debug
1606055262283.png
 
Status
Not open for further replies.
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
M How to setup outlook after importing old account information - Entering email account info creates with "(1)" after the account! Using Outlook 1
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 Using Outlook 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
A How do I ensure my BCM information get transferred BCM (Business Contact Manager) 2
A Updating contacts with new information BCM (Business Contact Manager) 1
H popup window "Location Information" Microsoft Office Outlook 2007 Using Outlook 3
K Linking Information from Accounts to Business Contacts Manager BCM (Business Contact Manager) 1
B Insert information to MailItem Outlook VBA and Custom Forms 1
K Extracting Calendar information from a OST file? Exchange Server Administration 5
S Need: Date handling in Outlook Macros, either information/documentation Outlook VBA and Custom Forms 1
T Get FreeBusy information for a Room/resource Outlook VBA and Custom Forms 1
T populating fields with information from the address book Outlook VBA and Custom Forms 5
Q Smart Tags - Telephone Number recognizing the contact information Outlook VBA and Custom Forms 3
R Custom form retains old information Outlook VBA and Custom Forms 2
D Lifelong Windows user - new to Mac - Help!!! Using Outlook 3
Witzker Outlook 2019 Macro GoTo user defined search folder Outlook VBA and Custom Forms 6
W OL giving basic auth when logged in to Win as specific user Using Outlook 0
Witzker Outlook 2019 How to get a Photo in a User Defined Contact form Outlook VBA and Custom Forms 2
Witzker Outlook 2019 Macro to send an Email Template from User Defined Contact Form Outlook VBA and Custom Forms 0
Witzker Outlook 2019 Edit contact from email does not open the user defined contactform Using Outlook 3
Witzker Set Cursor & Focus from any field to the body of a user Contact form in OL 2019 Outlook VBA and Custom Forms 1
Witzker Place cursor at opening, a user defined OL contact form Outlook VBA and Custom Forms 3
ill13 Prepend the user's computer name to the subject. Outlook VBA and Custom Forms 1

Similar threads

Back
Top