-
extract email address from sent folder
My co-worker works in Customer Service, they need to send out a daily Customer Satisfaction email Survey, to all people that emails were responded to that day, or maybe there is a way to popup a MsgBox and ask for a Date Range ?
Anyways, this would look only into the SentFolder, we use Outlook 2007 here. At the very minimum I would like to have a way to extract those emails (so if a customer was emailed that day say 4 times, I only want to extract one unique instance of that email address, not extract it 4 times) and then we can make a template and send them BCC.
But if there is a way to also have a template done, please let me know.
I have one script that looks into a column in Excel that has emails, grabs them and then I have a HTML template, and sends that over Outlook, so I know this kind of automation can be done, I just don't know how.
Please let me know if you guys can help or point me in the right direction. I know VBA for Access and Excel so so, but not an expert on Outlook , not refering to Outlook Express, just the regular one that comes with the Office Suite.
Ray
-
Does this get you started
[vba]
Sub GetRecipients()
Const oFolderSentMail As Long = 5
Dim oOL As Object
Dim oNameSpace As Object 'Outlook.NameSpace
Dim oSentMail As Object 'Outlook.MAPIFolder
Dim oItem As Object
Dim oColl As Collection
On Error Resume Next
Set oOL = GetObject(, "Outlook.Application")
On Error GoTo 0
If oOL Is Nothing Then
Set oOL = CreateObject("Outlook.Application")
'make Outlook visible
oSentMail.Display
End If
Set oNameSpace = oOL.GetNamespace("MAPI")
Set oSentMail = oNameSpace.GetDefaultFolder(oFolderSentMail)
Set oColl = New Collection
For Each oItem In oSentMail.items
oColl.Add oItem.to, oItem.to
Next oItem
Set oColl = Nothing
Set oSentMail = Nothing
Set oNameSpace = Nothing
Set oOL = Nothing
End Sub
[/vba]
-
Thank you very much, I will digest this with great interest, when I have time this week, I will come back here and ask any questions.
Again, THANK YOU.
-
Ok, I am not an expert in Outlook VBA, but a lot of this makes sense as VBA for Excel and Access, kinda share similarities.
From Outlook 2007 (is what I am using RETAIL version), Ok, I press:
Tools -- Macros -- VBA (Alt + F11) and I insert a module and then I pasted all the code under the module, when I compiled the code, there were no errors:
When I run the code from the Macro, it gave me a run-time error:
Run-time error '429'
ActiveX component can't create object
and in the VBA, under the module it pointed to this line:
Set oOL = CreateObject("Outlook.Application")
What is this supposed to do ? where will the email address list go once it grabs them ?
Thank you very much.
-
Actually, I adapted this from an Excel automation module I had. If doing it directly in Outlook, you can probably simplify it to
[vba]
Sub GetRecipients()
Dim oNameSpace As Object 'Outlook.NameSpace
Dim oSentMail As Object 'Outlook.MAPIFolder
Dim oItem As Object
Dim oColl As Collection
Set oNameSpace = Application.GetNamespace("MAPI")
Set oSentMail = oNameSpace.GetDefaultFolder(oFolderSentMail)
Set oColl = New Collection
For Each oItem In oSentMail.items
oColl.Add oItem.to, oItem.to
Next oItem
Set oColl = Nothing
Set oSentMail = Nothing
Set oNameSpace = Nothing
End Sub
[/vba]
The names will get stored in the collection coll. My code does nothing with them, just captures them.
-
I see, that first one from Excel, also give me that error as well, when I run it out of Excel. Thanks.
-
I tried running second one as a Macro from within Outlook, I get this error:
run-time error '5'
Invalid procedure call or argument
on the line that reads:
Set oSentMail = oNameSpace.GetDefaultFolder(oFolderSentMail)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules