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

Ray

[Bob Phillips]

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]

[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 ?

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

[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.

[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)