atljarman
asked on
Outlook VBA reply all with GLOBAL unresolved emails
Hi,
I have an outlook (2003 or 2010) VBA function that I am working on is one that reads the selected email, replies to all, and populates the CC list with everyone that was either in the to or cc list. The problem that I've run into is that for some of the folks who are in our global address book other than lastname, first, I get a relative address not the actual email address in the cc box. What I would like is to just get the email address.
Here is the macro to reply:
Sub ERCCommReply()
On Error GoTo ErrorHandler
Dim olkMsg As Outlook.MailItem, olkMsg2 As Outlook.MailItem, olkRpl As Outlook.MailItem, olkRcp As Outlook.Recipient, olkAdd As Outlook.Recipient, intIndex As Integer
Select Case TypeName(Application.Activ eWindow)
Case "Explorer"
'Set olkMsg = Application.ActiveExplorer .Selection (1)
Set olkMsg = Application.ActiveExplorer .Selection .Item(1)
'Set olkMsg2 = Application.ActiveExplorer .Selection .Item(1)
Set olkRpl = Application.ActiveExplorer .Selection .Item(1)
Case "Inspector"
Set olkMsg = Application.ActiveInspecto r.CurrentI tem
'Set olkMsg2 = Application.ActiveInspecto r.CurrentI tem
Set olkRpl = Application.ActiveInspecto r.CurrentI tem
End Select
Set olkRpl = olkMsg.ReplyAll
If ActiveExplorer.Selection.C ount > 1 Then
MsgBox "To ensure proper processing" & vbCrLf & "please select one email only.", , "Too many emails selected!"
GoTo ProgramExit
End If
Dim Signature As String
If Dir(MySig) <> "" Then
Signature = GetBoiler(MySig)
Else
Signature = ""
End If
olkRpl.BodyFormat = olFormatHTML
olkRpl.HTMLBody = olkMsg.HTMLBody
For intIndex = olkRpl.Recipients.Count To 1 Step -1
olkRpl.Recipients.Item(int Index).Del ete
Next
olkRpl.CC = ERCComm
Set olkRcp = olkRpl.Recipients.Add(olkM sg.SenderE mailAddres s)
olkRcp.Type = olTo
For Each olkAdd In olkMsg.Recipients
If olkAdd.Name <> Session.CurrentUser And InStr(ERCCommNames, olkAdd.Name) = 0 Then
'If olkAdd.Name <> Session.CurrentUser And InStr(ERCCommNames, olkAdd.Name) = 0 _
And InStr(ERCComm, olkAdd.Address) = 0 Then
Set olkRcp = olkRpl.Recipients.Add(olkA dd.Address )
Select Case olkAdd.Type
Case olOriginator
olkRcp.Type = olTo
Case Else
olkRcp.Type = olCC
End Select
End If
Next
'MsgBox olkRpl.CC & " to:" & olkRpl.To
MyInput2 = InputBox("If there is an CASE Number associated with this message, provide it below (number only)", "CASE Number?")
MsgBox "made it here"
subject = ""
'Append the require info to the subject line
'If the For official Use Only is in the subject line, do not add it again.
'If InStr(UCase(olkMsg.subject ), ERSubjectLine) Then
If InStr(UCase(olkRpl.subject ), ERSubjectLine) Then
Else
'subject = ERSubjectLine & " - " & olkMsg.subject
subject = ERSubjectLine & " - " & olkRpl.subject
End If
If InStr(UCase(olkRpl.subject ), MyInput2) Or MyInput2 = "" Then
'If InStr(UCase(Msg.Forward.su bject), MyInput2) Or MyInput2 = "" Then
'If InStr(UCase(Msg.Forward.su bject), MyInput2) Then
Else
subject = "CASE-" & MyInput2 & ": " & subject
End If
If olkMsg.Attachments.Count > 0 Then
Dim result
result = MsgBox("Do you want to include the current attachments?", vbYesNo + vbQuestion)
If result = vbYes Then
CopyAttachments olkMsg, olkRpl
End If
End If
olkRpl.BodyFormat = olFormatHTML
olkRpl.Recipients.ResolveA ll
olkRpl.HTMLBody = olkMsg.Forward.HTMLBody
olkRpl.subject = subject
olkRpl.Display
Set olkMsg = Nothing
Set olkMsg2 = Nothing
Set olkRpl = Nothing
Set olkRcp = Nothing
Set olkAdd = Nothing
ProgramExit:
Exit Sub
ErrorHandler:
If Err.Number = "-2147352567" Then
MsgBox "You must select/hightlight/set the focus on a message " & vbCrLf & "from the list (e.g., Inbox) before clicking this button." & vbCrLf & vbCrLf & _
"If you are currently looking at an open message, " & vbCrLf & _
"you will continue to recieve this message.", vbOKOnly, "Select a message from the list."
Else
MsgBox Err.Number & " - " & Err.Description
End If
Resume ProgramExit
End Sub
I have an outlook (2003 or 2010) VBA function that I am working on is one that reads the selected email, replies to all, and populates the CC list with everyone that was either in the to or cc list. The problem that I've run into is that for some of the folks who are in our global address book other than lastname, first, I get a relative address not the actual email address in the cc box. What I would like is to just get the email address.
Here is the macro to reply:
Sub ERCCommReply()
On Error GoTo ErrorHandler
Dim olkMsg As Outlook.MailItem, olkMsg2 As Outlook.MailItem, olkRpl As Outlook.MailItem, olkRcp As Outlook.Recipient, olkAdd As Outlook.Recipient, intIndex As Integer
Select Case TypeName(Application.Activ
Case "Explorer"
'Set olkMsg = Application.ActiveExplorer
Set olkMsg = Application.ActiveExplorer
'Set olkMsg2 = Application.ActiveExplorer
Set olkRpl = Application.ActiveExplorer
Case "Inspector"
Set olkMsg = Application.ActiveInspecto
'Set olkMsg2 = Application.ActiveInspecto
Set olkRpl = Application.ActiveInspecto
End Select
Set olkRpl = olkMsg.ReplyAll
If ActiveExplorer.Selection.C
MsgBox "To ensure proper processing" & vbCrLf & "please select one email only.", , "Too many emails selected!"
GoTo ProgramExit
End If
Dim Signature As String
If Dir(MySig) <> "" Then
Signature = GetBoiler(MySig)
Else
Signature = ""
End If
olkRpl.BodyFormat = olFormatHTML
olkRpl.HTMLBody = olkMsg.HTMLBody
For intIndex = olkRpl.Recipients.Count To 1 Step -1
olkRpl.Recipients.Item(int
Next
olkRpl.CC = ERCComm
Set olkRcp = olkRpl.Recipients.Add(olkM
olkRcp.Type = olTo
For Each olkAdd In olkMsg.Recipients
If olkAdd.Name <> Session.CurrentUser And InStr(ERCCommNames, olkAdd.Name) = 0 Then
'If olkAdd.Name <> Session.CurrentUser And InStr(ERCCommNames, olkAdd.Name) = 0 _
And InStr(ERCComm, olkAdd.Address) = 0 Then
Set olkRcp = olkRpl.Recipients.Add(olkA
Select Case olkAdd.Type
Case olOriginator
olkRcp.Type = olTo
Case Else
olkRcp.Type = olCC
End Select
End If
Next
'MsgBox olkRpl.CC & " to:" & olkRpl.To
MyInput2 = InputBox("If there is an CASE Number associated with this message, provide it below (number only)", "CASE Number?")
MsgBox "made it here"
subject = ""
'Append the require info to the subject line
'If the For official Use Only is in the subject line, do not add it again.
'If InStr(UCase(olkMsg.subject
If InStr(UCase(olkRpl.subject
Else
'subject = ERSubjectLine & " - " & olkMsg.subject
subject = ERSubjectLine & " - " & olkRpl.subject
End If
If InStr(UCase(olkRpl.subject
'If InStr(UCase(Msg.Forward.su
'If InStr(UCase(Msg.Forward.su
Else
subject = "CASE-" & MyInput2 & ": " & subject
End If
If olkMsg.Attachments.Count > 0 Then
Dim result
result = MsgBox("Do you want to include the current attachments?", vbYesNo + vbQuestion)
If result = vbYes Then
CopyAttachments olkMsg, olkRpl
End If
End If
olkRpl.BodyFormat = olFormatHTML
olkRpl.Recipients.ResolveA
olkRpl.HTMLBody = olkMsg.Forward.HTMLBody
olkRpl.subject = subject
olkRpl.Display
Set olkMsg = Nothing
Set olkMsg2 = Nothing
Set olkRpl = Nothing
Set olkRcp = Nothing
Set olkAdd = Nothing
ProgramExit:
Exit Sub
ErrorHandler:
If Err.Number = "-2147352567" Then
MsgBox "You must select/hightlight/set the focus on a message " & vbCrLf & "from the list (e.g., Inbox) before clicking this button." & vbCrLf & vbCrLf & _
"If you are currently looking at an open message, " & vbCrLf & _
"you will continue to recieve this message.", vbOKOnly, "Select a message from the list."
Else
MsgBox Err.Number & " - " & Err.Description
End If
Resume ProgramExit
End Sub
ASKER
I get a relative reference like "/O=MDA/OU=First Administrative Group/cn=Recipients/cn=Bob Cates;" from the global address book.
It's like it knows the relative reference to the contact information in the global address book but rather than pull the actual email address it pulls the relative reference. When in all other instances when the name is listed as last, first, then the email address actually populates.
The email in outlook, using the example above might look like: Arizona-Cates, Bob and the underlying email address in the original email (when double clicking on the name from the original email) is bcates@arizona.gov. So my question is, when using this macro, why does Outlook pull "/O=MDA/OU=First Administrative Group/cn=Recipients/cn=Bob Cates;"
instead of bcates@arizona.gov in these instances, or how to ensure that the actually email address is grabbed not this relative reference.
It's like it knows the relative reference to the contact information in the global address book but rather than pull the actual email address it pulls the relative reference. When in all other instances when the name is listed as last, first, then the email address actually populates.
The email in outlook, using the example above might look like: Arizona-Cates, Bob and the underlying email address in the original email (when double clicking on the name from the original email) is bcates@arizona.gov. So my question is, when using this macro, why does Outlook pull "/O=MDA/OU=First Administrative Group/cn=Recipients/cn=Bob
instead of bcates@arizona.gov in these instances, or how to ensure that the actually email address is grabbed not this relative reference.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is there a way to detect if the user is using 2003 to prevent the function from firing?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will give this a try later today. I am going to post a new question shortly in looking at pulling names from a distribution list. I have another function that checks to see if the person was already copied on the email then if they were, then don't add them. If they were not, then look add the remaining names/emails from the distribution list.
Ok. If you post a link to that question here, then I'll take a look.
ASKER
This is what I've modified in the code:
Compile Error: Type Mismatch highligting olkAdd.Address of the GetSMTPAddress function. I think that there could be another definition like olkAdd.Address.email or something, but not sure what. Any thoughts.
If GetOutlookVersion() >= 14 Then
Set olkRcp = olkRpl.Recipients.Add(GetSMTPAddress(olkAdd.Address))
Else
Set olkRcp = olkRpl.Recipients.Add(olkAdd.Address)
End If
Compile Error: Type Mismatch highligting olkAdd.Address of the GetSMTPAddress function. I think that there could be another definition like olkAdd.Address.email or something, but not sure what. Any thoughts.
ASKER
Ok... I've tried a couple of things.
If GetOutlookVersion() >= 14 Then
Set olkRcp = olkRpl.Recipients.Add(GetS MTPAddress (olkMsg))
Else
Set olkRcp = olkRpl.Recipients.Add(olkA dd.Address )
End If
is the only thing that seems to not get the type mismatch but inserts the sender the same number of times that ther are receipients in the original email. It's kind of funny, but not helpful.
Is there a way to modify the function so it will just loop through the olkRpl.Recipients? or use the OlkAdd.Address (e.g., String vs oulook item)
If GetOutlookVersion() >= 14 Then
Set olkRcp = olkRpl.Recipients.Add(GetS
Else
Set olkRcp = olkRpl.Recipients.Add(olkA
End If
is the only thing that seems to not get the type mismatch but inserts the sender the same number of times that ther are receipients in the original email. It's kind of funny, but not helpful.
Is there a way to modify the function so it will just loop through the olkRpl.Recipients? or use the OlkAdd.Address (e.g., String vs oulook item)
Could you post your code as you have it now so I can take a look? If so, please embed it in the code tags using the code button on the toolbar above. That makes the code much easier to read.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. I took your suggestion and had to work it around to get the actual code to work.
You're welcome!
I'm not clear on what you mean when you say "... I get a relative address not the actual email address". What does relative address mean?