extract email address from sent folder

[RompStar]

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 :dunno , not refering to Outlook Express, just the regular one that comes with the Office Suite.

Ray

[Bob Phillips]

Does this get you started



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

[RompStar]

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.

[RompStar]

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 ? :wot :help

Thank you very much.

[Bob Phillips]

Actually, I adapted this from an Excel automation module I had. If doing it directly in Outlook, you can probably simplify it to



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


The names will get stored in the collection coll. My code does nothing with them, just captures them.

[RompStar]

I see, that first one from Excel, also give me that error as well, when I run it out of Excel. Thanks.

[RompStar]

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)