Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Let's try this again (OL2002/SP2)

    Very new to VBA, but longtime programmer. I know what I want to achieve, just need some help getting there. Perhaps the best way is to start with my flowchart and see if this is the right way to attack this problem.

    1 - In short, I am trying to automate mailings to clients.
    2 - Each client has a unique frequency of mailings. (Field 'Mailing Frequency' specifies days between mailings.)
    3 - When I mail, I create a journal entry for tracking purposes.
    4 - I would like to send mailings weekly to those contacts who are due for a mailing

    Here is what I am thinking:

    Create a macro that I will run once per week.
    (Begin loop)
    Loop thru all contacts in a specified folder whose 'Mailing Frequency' is greater than 0
    For each such contact, search journal for most recent mailing to that contact
    Add 'Mailing Frequency' days to the date of that journal entry
    If resulting date is today or before, add contact to list of contacts to be mailed
    (End of loop)
    Next, merge resulting list of contacts into a Word mailing label template for printing
    Then loop thru list and create journal entry for each contact to track mailing

    I know that might be a little involved, but at this point I'm just looking for critiques on the process. I don't want to charge into this if I'm headed in the wrong direction.

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Let's try this again (OL2002/SP2)

    A Journal entry of mailings for each Contact might generate a huge Journal volume. I'd probably keep Mailing Frequency as a Contact Category and Last Mailing Date on a Contact User-defined Field. Then you can filter & select Contacts by Mailing Frequency Category, read the Last Mailing Date Field, run the Mail Merge to a Word Doc (note that there is Mail Merge capability in Outlook), and update the Last Mailing Date Field for the Contacts. One problem I can immediately think of with my approach is if you have one Contact who should recieve multiple different newletters on different time frequencies; then Last Mailing Date will be a problem.

    But it's your party. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Let's try this again (OL2002/SP2)

    No, John. Appreciate the advice.

    I'm thinking about using your suggestion and recording only the last mailing, and calculating the 'next mailing' date from that. (When I print labels, I'll have to create a function to change last mailing date to 'today'.)

    Again, a question before I dig in too deep... Should the "Next Mailing Date" be calculated in the form as a VBScript formula, or in VBA, or some other way? And what would be the initiating event? I want the "next mailing date" to be current. I don't want to have to open the form for it to update. I'd also like to be able to search ad sort by this field. How do I go about this?

    Thanks again!

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Let's try this again (OL2002/SP2)

    Followup question:

    Before I completely abandon my initial idea...
    Assuming I have 200-300 contacts, and I mailed them 4-8 times per year, that gives a total of around 1500 mailing-related journal entries per year. Is that enough to cause the "huge journal volume" problems you mentioned?
    (I'd also average around 3 telephone journal entries per contact per year.)

    (For whatever reason), I'd like to continue the system I have in place unless that number of journal items will cause significant problems.

    Thanks!

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Let's try this again (OL2002/SP2)

    That Journal volume should be OK; when I was replying, for no reason at all I guessed that your frequencies would be a mix of weekly and monthly, and I also admit to a prejudice against Journaling because it tends to bloat the PST.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Let's try this again (OL2002/SP2)

    I can understand that, as it (journal entries) does seem to slow down searches, etc. somewhat. However, I'd like to use Outlook as a basic contact manager. It has many features built-in, but some very logical ones are missing. I'm hoping to work around that.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Let's try this again (OL2002/SP2)

    FWIW my preferred references for Outlook matters are this Outlook Forum (naturally) and the VBA Forum in the Lounge, Sue Mosher's Slipstick and it's sibling outlookcode, Sue's book which you'll find referenced on those sites, and I also find the Randy Byrne's Outlook Object Model maps at Microeye to be helpful; his book is the Outlook programming bible, but I don't have it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Let's try this again (OL2002/SP2)

    Ok, I've dug in and gotten stuck. Perhaps the best way to explain my difficulty is to post the code:

    Sub cmdShowMailings_Click()
    Dim olApp
    Dim olSession
    Dim olJournalFolder
    Dim strSearchContact


    'Initialize count of folders searched
    lCountOfFound = 0



    ' Get a reference to the Outlook application and session.
    Set olApp = Application
    Set olSession = olApp.GetNamespace("MAPI")
    Set olJournalFolder = olSession.GetDefaultFolder(olFolderJournal)



    'Set strSearchContact =

    'text list field on form: lstSentMailings



    strSearchContact = "Clark"



    ' Loop through journal folder
    For i = olJournalFolder.Items.Count To 1 Step -1
    Set olTempItem = olJournalFolder.Items(i)
    ' Check to see if a match is found
    If InStr(1, olTempItem.Type, "Mailed", 0) > 0 Then
    If InStr(1, olTempItem.Subject, strSearchContact, 0) > 0 Then
    MsgBox "Found message: " & olTempItem.Subject & " :: " & olTempItem.Type & " :: " & olTempItem.Start
    lCountOfFound = lCountOfFound + 1
    End If
    End If
    Next


    MsgBox CStr(lCountOfFound) & " messages were found."


    End Sub




    What I have decided is that I would like to continue using Journal entries to track mailings and phone calls. However, I'd like to only see specified items, and only when I request them, rather than getting a pile of info on the Activities tab. That is where this code came from. I created it (from examples) in the VBA editor. But then I got the impression that maybe this should be done in VBScript rather than VBA. (Is this the correct assumption?)

    My thought was to initiate the search with a button. User clicks on a button called "Show Past Mailings", and a list box is populated with journal entries containing the word "mailed" in the entry type, with their start date. Right now I'm testing it to search for people named "Clark", but I'd like that to be replaced with the name of the open contact.

    Make any sense?

    Thanks in advance

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Let's try this again (OL2002/SP2)

    Scott, I'm not going to be able to help you much because I don't have comparable testbed Journals, and I'm not familiar with the JournalItem object model. A couple of comments & questions.

    Using Option Explicit will force you to correctly Type (Dim) your data, and in addition to providing better & faster code, helps you use VBE Intellisense to obtain Properties. Attached is a rewritten example of your code, except, I have no Journal records to search. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I also took the liberty of simplifying some of your code in the attachment, but it's not necessarily valid.

    In the line

    If InStr(1, olTempItem.Type, "Mailed", 0) > 0 Then

    where olTempItem is the indexed Journal Item, "Mailed" is not a Type Property. Is "Mailed" a Custom Journal Property you created for your Journal Items? If so you'll have to use UserProperties Property; see the VBA Help for using the UserProperties Collection Object. You'll need to create loop through all UserProperties for each Item looking for the "Mailed" Property and returning its setting.

    HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Feb 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Let's try this again (OL2002/SP2)

    John -

    I apologize for the delay in responding to your helpful post. (I have *way* too many irons in the fire!)

    Your suggestions got me down the right path, and helped me to understand a few commands better. I also ended up purchasing Sue Mosher's book, MS Outlook Programming, and although I hate to take the time to learn something, it was the right thing to do.

    I currently have a working version that I moved to VBS so that it will run behind the scenes whenever I view a contact. It helps in my marketing efforts tremendously. I have even expanded to write routines and view filters so that I can see every day who I need to mail and who is due for a followup call. I know there is plenty of room for improvement, but that will wait for another day!

    Thanks again for all your help!

    Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •