0

anyone know how to retrieve CC email addresses from a mail in an Outlook folder using VBA? I have tried the below codes I've found but I can't seem to make it work as I'm having this error enter image description here

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
Xev
  • 99
  • 1
  • 1
  • 11
  • 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
  • 57,446
  • 3
  • 49
  • 77