0

How do I retrieve CC email addresses from a mail in an Outlook folder using VBA?

I have this error

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

Sub CC_EMAIL()
Dim lngCounter As Long
lngCounter = 2
Const PR_EMAIL = &H39FE001E
ThisWorkbook.Sheets(1).Cells(1, 1).Value = "CC Name"
ThisWorkbook.Sheets(1).Cells(1, 2).Value = "CC Email"
'ThisWorkbook.Sheets(1).Cells(1, 3).Value = "Cc-Recipients"
Set objOL = CreateObject("Outlook.Application")
Set objMsg = objOL.ActiveInspector.CurrentItem
Set objSmail = CreateObject("Redemption.SafeMailItem")
objSmail.Item = objMsg
For Each recip In objSmail.Recipients
    If InStr(objSmail.CC, recip.Name) Then
    ThisWorkbook.Sheets(1).Cells(lngCounter, 1).Value = recip.Name
    ThisWorkbook.Sheets(1).Cells(lngCounter, 2).Value = recip.Fields(PR_EMAIL)
    'ThisWorkbook.Sheets(1).Cells(lngCounter, 3).Value = objSmail.CC
    lngCounter = lngCounter + 1
    End If
Next
End Sub
Community
  • 1
  • 1
Xev
  • 109
  • 1
  • 1
  • 12
  • What line is returning the error? That will give you a hint as to where the problem lies. You should see a line highlighted when the code stops with the error message. – Ron Rosenfeld May 19 '22 at 11:00
  • @RonRosenfeld it's ```Set objMsg = objOL.ActiveInspector.CurrentItem``` but I have no idea what's the problem with it – Xev May 19 '22 at 11:04
  • Perhaps if you explain how you are selecting the email item from which you want to extract the CC's, I can understand better what you are trying to do. In the past, I've selected email items from a Folder object. – Ron Rosenfeld May 19 '22 at 11:21
  • There is no object named objMsg when you have just opened Outlook. If the code was written in Outlook VBA instead you would open a mailitem before running the code. Search for examples where Outlook calls Excel. – niton May 19 '22 at 12:36
  • If you start in Excel see https://stackoverflow.com/questions/11151811/reference-a-folder-by-name to specify a folder. After this you could loop through the items in the folder. – niton May 19 '22 at 12:55

1 Answers1

0

Firstly, check that objOL.ActiveInspector is not null. It will exits only if there an email shown in a separate inspector.

Secondly, to test whether a recipient is CC, use the Type property. Change the line

If InStr(objSmail.CC, recip.Name) Then

to

If recip.Type = 2 Then '2 is olCC
Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78