Solved

I need macro to scan all calendar events and pull out the email addys

Posted on 2010-11-23
12
213 Views
Last Modified: 2013-02-06
I need macro to scan all calendar events and pull out the email addys
0
Comment
Question by:Caiapfas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
12 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34202381
Hi, Caiapfas.

Pull out what addresses?  The organizer's address for meetings?  The addresses of other meeting attendees?  Both?  What do you want to do with the addresses?
0
 
LVL 2

Author Comment

by:Caiapfas
ID: 34202461
good question, i need the macro to pull out of content of the appt.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34202548
Sorry, I don't understand.  Can you clarify?  Also, what do you want to do with whatever is pulled out?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Author Comment

by:Caiapfas
ID: 34202975
the part of the appt that you could type a book.
i have email addys in there

dump them to a cvs file
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34204325
Oh, so you want to pull email addresses from the notes section of the appointment.  That's different and a bit more difficult.  I'll put something together and post it as soon as I can, hopefully by the end of the day.
0
 
LVL 2

Author Comment

by:Caiapfas
ID: 34206553
you rock, thanks for all the help!
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 34448213
Clearly that took me a lot longer than I had said.  I apologize for that.

Here's the code.  Follow these instructions to add it to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

To use the code

1.  Run the macro GetAddressesFromAppointments
Sub GetAddressesFromAppointments()
    'On the next line edit the path and name of the file'
    Const FILE_NAME = "C:\eeTesting\Addresses.csv"
    Dim olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, strAddresses As String, objFSO As Object, objFile As Object
    Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items
    With olkItems
        .Sort "[Start]"
        .IncludeRecurrences = True
    End With
    For Each olkAppt In olkItems
        If (Year(olkAppt.Start) <= Year(Date)) And (Month(olkAppt.Start) <= Month(Date)) And (Day(olkAppt.Start) <= Day(Date)) Then
            strAddresses = strAddresses & GetEmailAddresses(olkAppt.Body)
        End If
        DoEvents
    Next
    Set olkItems = Nothing
    Set olkAppt = Nothing
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(FILE_NAME)
    objFile.WriteLine Mid(strAddresses, 1, Len(strAddresses) - 1)
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    msgbox "Done"
End Sub

Function GetEmailAddresses(strBody As String) As String
    Dim objRegEx As Object, colMatches As Object, varMatch As Variant
    Set objRegEx = New RegExp
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = "([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})"
        Set colMatches = .Execute(strBody)
    End With
    For Each varMatch In colMatches
        GetEmailAddresses = GetEmailAddresses & varMatch & ","
    Next
    Set objRegEx = Nothing
    Set colMatches = Nothing
End Function

Open in new window

0
 
LVL 2

Author Comment

by:Caiapfas
ID: 35985008
will give this a try
0
 
LVL 18

Expert Comment

by:Karen Falandays
ID: 38860315
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 38860316
I object.  The code I posted does exactly what the OP asked for.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question