Outlook VBA Error when sender is group email

0
votes

I have the following to identify the Sender email address and Name (which I then store for later use):

strSender = itm.Sender.GetExchangeUser().PrimarySmtpAddress
strSenderName = itm.Sender

This works fine, but when a group email address is used (i.e. one that multiple users can send emails from, e.g. [email protected]), I get an error message like this:

Run-time error '91':
Object variable or With block variable not set

Any idea how to resolve this?

Full script:

Public Sub saveAcct(itm As Outlook.MailItem)
Const Filepath1 = "C:\Users\tenba1\Documents\QlikView\Account Recons\Recon_Acct.txt"
Const Filepath2 = "C:\Users\tenba1\Documents\QlikView\Account Recons\Recon_Acct_Sender.txt"
Const Filepath3 = "C:\Users\tenba1\Documents\QlikView\Account Recons\Recon_Acct_SenderName.txt"
Const ForWriting = 2

strAccNumber = Trim(Mid(itm.Subject, InStrRev(itm.Subject, " "), Len(itm.Subject) - InStr(1, itm.Subject, " ")))
strSender = itm.Sender.GetExchangeUser().PrimarySmtpAddress
strSenderName = itm.Sender


Do Until FileExists("C:\Users\tenba1\Documents\QlikView\Account Recons\DONE.txt")
Loop

'Create a Busy file:
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("C:\Users\tenba1\Documents\QlikView\Account Recons\BUSY.txt", True)
MyFile.Close

'Delete the DONE file:
aFile = "C:\Users\tenba1\Documents\QlikView\Account Recons\DONE.txt"
Kill aFile

'Update the Account Number File:
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO1.OpenTextFile(Filepath1, ForWriting)
objFile1.Write ("SET vAcct = '" & strAccNumber & "';")

'Update the Sender Email Address File:
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objFile2 = objFSO2.OpenTextFile(Filepath2, ForWriting)
objFile2.Write (strSender)

'Update the Sender Name File:
Set objFSO3 = CreateObject("Scripting.FileSystemObject")
Set objFile3 = objFSO3.OpenTextFile(Filepath3, ForWriting)
objFile3.Write (strSenderName)

'Launch the PowerShell Script for creating the recon file:
Dim shell
Set shell = CreateObject("wscript.shell")
shell.Run "C:\Users\tenba1\Documents\Scripts\Account_Recon.bat"

End Sub
2
vbaoutlook

2 Answers

2
votes

You never check if GetExchangeUser() returns a valid object. For a one-off SMTP object it will return null.

Update: try something like the following:

strSender = ""
strSenderName  = itm.SenderName
If itm.SenderEmailAddress = "EX" Then
  set objSender = itm.Sender
  If Not (objSender Is Nothing) Then
    set objExchUser = Sender.GetExchangeUser()
    If Not (objExchUser Is Nothing) Then
      strSender = objExchUser.PrimarySmtpAddress
    End If
  End If
Else
  strSender = itm.SenderEmailAddress
End If
0
votes

The key piece of Dmitry's response is:

set objExchUser = Sender.GetExchangeUser()
If Not (objExchUser Is Nothing) Then <do something>

The check for sender is preliminary, and solves a different problem than the one being asked by @user2725402.

As Dmitry suggests in his first sentence, the problem with group emails is the lack of a valid Exchange User. Microsoft documentation on the GetExchangeUser method states that it "Returns Null (Nothing in Visual Basic) if the AddressEntry object does not correspond to an Exchange user." So this is where you want to check group email addresses for a null value.

SUMMARY:
To solve this kind of issue, check for a valid Exchange User and only proceed if you know there is one:

    If Msg.SenderEmailType = "EX" Then 'email is from within your Exchange
        Dim objExchangeUser As Object
        Set objExchangeUser = Msg.Sender.GetExchangeUser()
        If objExchangeUser Is Nothing Then Exit Sub <or other appropriate action>
    End If