[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

CUSTOM ACTION OR MACRO?

I have orders that I email out each day. The only thing (usually) in the body is "DUE BY date". Is there a way to set up a rule or macro or something that will look @ the date in the body and set a reminder on my calendar? There's too many each day for me to do each individually in the calendar so I was hoping I could automate it.
0
vtbottombear
Asked:
vtbottombear
  • 12
  • 11
1 Solution
 
David LeeCommented:
Hi vtbottombear,

We can do it with a macro so long as there's something unique about the message we can key on.  Are you sure you want it on your calendar and not as a task?  Both support reminders.

Cheers!
0
 
vtbottombearAuthor Commented:
I would prefer calendar but either would work.
0
 
David LeeCommented:
Ok.  I was just thinking that it'd really clutter your calendar up to have all these extraneous appointment, that's what they'd have to be, on it.  What format are the messages in (plain text, HTML, Rich-Text) and what can I use to pick these messages out?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
vtbottombearAuthor Commented:
They are plain text I beleive. I'm sending individual PDF files to 1 of 2 addresses. I usually put "Due by ##/##"  but I can use something different if it helps to pick out these specific messages.
0
 
David LeeCommented:
Is the due by date a day and month, or a month and year?
0
 
vtbottombearAuthor Commented:
Month/Day
0
 
David LeeCommented:
Ok.  I'll try to get the code for this posted inside the next 24 hours.
0
 
David LeeCommented:
vtbottombear,

Sorry to be slow.  Here's the code for doing this.  Follow these instructions to use it.
1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code below and paste it into the right-hand pane of the VB Editor window
5.  Click the diskette icon on the toolbar to save the changes
6.  Close the VB Editor
7.  Click Tools->Macro->Security
8.  Set the Security Level to Medium.  
9.  Close Outlook
10.  Start Outlook
11.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.
12.  Test the macro by sending one of these messages.

There are three constraints to using this code.
1.  The messages must be in plain text format.  Anything else will cause unpredictable results.
2.  The body of these messages must contain a single line of text in the form "Due by xx/yy".  Case is immaterial.
3.  This will only work from the full version of Outlook, not OWA.  It will also only work while Outlook is open and running.

I set the apointment to be an all day event.  If you want them set to a particualr time, then I can adjust the code accordingly.  I didn't know what to use for the appointment subject, so I used the message subject.  If you want a different subject, then I can make that change too.

'Code Starts Here
Dim WithEvents olkSentItems As Outlook.Items

Private Sub Application_Quit()
    Set olkSentItems = Nothing
End Sub

Private Sub Application_Startup()
    Set olkSentItems = Session.GetDefaultFolder(olFolderSentMail).Items
End Sub

Private Sub olkSentItems_ItemAdd(ByVal Item As Object)
    Dim olkAppointment As Outlook.AppointmentItem, _
        arrWords As Variant, _
        arrDate As Variant, _
        intYear As Integer, _
        strDate As String
    If Item.Class = olMail Then
        If Item.BodyFormat = olFormatPlain Then
            arrWords = Split(Item.Body, " ")
            arrDate = Split(arrWords(2), "/")
            intYear = IIf(CInt(arrDate(0)) > Month(Date), Year(Date) + 1, Year(Date))
            Set olkAppointment = Application.CreateItem(olAppointmentItem)
            With olkAppointment
                .Subject = Item.Subject
                strDate = arrDate(0) & "/" & arrDate(1) & "/" & intYear
                .Start = CDate(strDate)
                .AllDayEvent = True
                .Save
            End With
        End If
    End If
    Set olkAppointment = Nothing
End Sub
0
 
vtbottombearAuthor Commented:
BlueDevil....
I put it all in and followed the directions. I get the macro warning but when I send the emails, nothing shows up in my calendar. I made sure they are in plain text and have Due By mm/dd in the body.
Also, when I goto Tools, Macro, Macros... nothing is listed there but it does show in the Visual Basic Editor. But it shows as a Project not a Macro. Did I do something wrong?

0
 
David LeeCommented:
It shouldn't show as a macro, so what you're seeing is correct.  Are the sent messages appearing in Sent Items?
0
 
vtbottombearAuthor Commented:
Yes they do show in the Sent Items folder
0
 
David LeeCommented:
Ok, time to do some debugging.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  In the right-hand window click on this line of code
        If Item.Class = olMail Then
5.  Press F9.  A red dot should appear in the margin to the left of that line of code.  This is called a breakpoint.
6.  Minimize, don't close, the VB Editor window.
7.  Send a message.  When it hits Sent Items the code should fire and the VB editor window will maximize with that line of code highlighted in yellow.  If it does, go on to step 8.  If it doesn't stop here and let me know.
8.  Pressing the F8 key will execute the line of code in yellow and move to the next line of code.  Keeping pressing F8 to execute a line of code and note the lines of code executed.  I need to know what path the code takes.  Once you know what path the code takes you can repeat step #5 to remove the breakpoint.  9.  Close the VB editor.
0
 
vtbottombearAuthor Commented:
OK I did that and the window didn't maximize

0
 
vtbottombearAuthor Commented:
Correction!!
I forgot my email has a 2 minute delay on sending messages. The VBA popped up and I hit F8. It gets to
If Item.BodyFormat = olFormatPlain Then

and skips to
End If

I've checked and the format is plain text. I did notice after that my anti virus adds a message that no virus was foiund. Is that causing the problem?
0
 
David LeeCommented:
Let's try this.  Insert the following line of code

    MsgBox "Message format = " & Item.BodyFormat

immediately before this line of code

    If Item.BodyFormat = olFormatPlain Then

Now, send another message.  When the code fires a dialog-box will appear showing the format of the message.  There are four possible values.  

0 = Unspecified format
1 = Plain text
2 = HTML
3 = Rich-text

Let me know what format is reports.  

> I did notice after that my anti virus adds a message that no virus was foiund.
Adds a message where?  
0
 
vtbottombearAuthor Commented:
OK I added that and the message is "0". I went and double checked the mail format and it is set to plain text.

I also changed my anti virus to not put in the comment that the email was checked.
0
 
David LeeCommented:
Hmmm.  Well, not sure why the message format is showing up as Unspecified, but we can modify the code easily to accept those messages.  Change this line of code

        If Item.BodyFormat = olFormatPlain Then

to

        If Item.BodyFormat = olFormatPlain Or Item.BodyFormat = olFormatUnspecified Then
0
 
vtbottombearAuthor Commented:
Getting closer! Now I get an untitled event on the calendar
0
 
David LeeCommented:
Is the message's subject line blank?
0
 
vtbottombearAuthor Commented:
No its always the order number
0
 
David LeeCommented:
Then I don't know how it's possible for the appointment subject to be blank.  This line of code

    .Subject = Item.Subject

sets the appointment subject to match the message subject.  If the message subject is not blank, then it would seem impossible for the appointment subject to appear blank.  But then I also cannot explain how the message can say they are plain text yet Outlook sees them as "Unspecified Format".  What version of Outlook are you using?
0
 
vtbottombearAuthor Commented:
Outlook 2002
0
 
vtbottombearAuthor Commented:
WOOHOO! It's working now! Thanks so much!!!!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now