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
Solved

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

Posted on 2010-11-23
12
210 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 17

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

If you don't know how to downgrade, my instructions below should be helpful.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

792 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