send message to Outlook for user alert

[samohtwerdna]

Hello all,

Another one of my ideas has got me pleading for help.

I have a schedule for orders that among other things has a column for the day the order was received ; a column for the date the order was processed and a final column for when the order is approved for production.

I already have an alert message on Workbook_Open that indicates the orders over a month old that have not yet been approved. I would like to be able to send alert messages to Outlook. I was thinking about making a little user form that pops up when a order received date is entered asking "would you like a reminder message" if the user picks yes then an Outlook reminder is created for them - The reminder would activate ~48 hrs after the received date is filled in and continue until the date approved is filled in for that order.

Any ideas about how to begin??

Andrew T. Adcock

[Killian]

Sounds like a good idea...
All you need to do is pass your alert message string to a routine that creates a mail item, and sends it - presumably, you'll have the recipient of the message stored somewhere in your data, so you can pass that as wellSub SendMailAlert(strMessage As String, strTo as String)

Dim olApp As Object
Dim olMailItem As Object

Set olApp = CreateObject("Outlook.Application")
Set olMailItem = olApp.createitem(0)
With olMailItem
.To = strTo
.Body = strMessage
.Send
End With

End Sub

'call like this
SendMailAlert "This is my message", "someone@somewhere.com"

[samohtwerdna]

Thanks Killian,

This is an interesting and helpful method, but when called it activates an annoying "This program is trying to send you something that could be a virus do you want to continue?" - I was hoping to send the message into Outlook and create a reminder through Outlook to the user - Though I may be able to get the mail to do the same thing - I thought an Outlook reminder might be simpler.

Also - How do I call my user form when column "f" is changed? is their a WorsheetColumn_Changed () or something? Or should I try a If Not IsEmpty(userRange) ??

[samohtwerdna]

I guess what I want is a calander task in Outlook not a reminder message:yes

[Killian]

Ahh yes, the OUtlook Object Model Guard... it's not like the old days...
But I there is a way of adding calander entries that I came across the other day at work - I'll take a look tomorrow and post back

Regarding column F changes:
The Worksheet_Change event gives you a reference to the cell that changed. Check if its in Col F.Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
MsgBox "do something"
End If
End Sub

[samohtwerdna]

OK - Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
frmRemider.Show vbModal
End If
End Sub
For when the "Date received" is filled in - Then a simple form pop's up asking "Would you like to be sent a reminder for this Job? and Has a txtRemind text box that fills in the message of the reminder then on send:

Private Sub cmdSend_Click()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem

Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = Date + 1 + TimeValue("8:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = "Job Reminder"
.Location = "Short Order Schedule"
.Body = txtRemind.Text
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 120
.ReminderSet = True
.Save
End With
End Sub

Which work pretty good, but How do I continue to send this reminder until the "Date Approved" cell is filled out for this Job? - And How would I cancel this reminder when the Job is Approved?

Any Thoughts

[Killian]

You can set the appointment to recur like this'declare an object variable for the recurrence
Dim olRecurPat As Outlook.RecurrencePattern

'inside thw olApt With block
Set olRecurPat = .GetRecurrencePattern
olRecurPat.RecurrenceType = olRecursDaily
olRecurPat.PatternStartDate = Date + 1
olRecurPat.NoEndDate = TrueIn terms of controlling what happens for new events - like the Approved cell being filled:
Outlook items have a unique identifier, for the appointment code you have, it would be olApt.EntryID
If you save this in the data for the order when its created, when the order is approved, you can get the EntryID, find the Appointment item and delete it.

[samohtwerdna]

Thanks a lot Killian! I'm almost there!

My problem now is saving the entryId I tried:
olApt.EntryID = ActiveCell.Offset(0, -2).Value
In the With olApt - Then I created a DeleteApt() function when the date approved was changed with a for loop like:

For Each olApt In olFldr.Items
If olApt.EntryID = ActiveCell.Offset(0, -4) Then
olApt.Delete
End If
Next olApt
This didn't delete my created appointments and when I debugged I saw that the olApt.EntryID was equal to some gaigantic number like 00023789AA 467828491 - So obviously I'm not setting the ID correctly

What is my problem??

[samohtwerdna]

OK I came up with a cheeter way.

I set the subject of my reminder like this:
olApt.Subject = "Job Reminder for" & ActiveCell.Offset(0, -2).Value
when I save it - So my For loop when I delete it just uses the same subject via:
For Each olApt In olFldr.Items
If olApt.Subject = "Job Reminder for" & ActiveCell.Offset(0, -4).Value Then
olApt.Delete
End If
Next olApt
This seemed to work fine - now when an order is created the user has the option of getting a reminder sent with a customized message as a recuring appointment, if multiple jobs are entered then the next appoint ment simply takes the next available time slot. And when the "date Approved" is changed the reminder is deleted.

I'm almost done I can feel it :thumb

[Killian]

The EntryID will indeed be gigantic (it's a uniqe ID in your install of Outlook)

First, one important thing I didn't mention is the EntryID won't exist until the Appointment item has been saved, so you'll need to set that after the .Save in the with block.

Next, it's not clear from your post how you've set the olFldr you're looking at for the appointment items - this need to be the Calender folder.
here's a complete example (so some of your variable declarations and initializations may be repeated)Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olCal As Outlook.MAPIFolder
Dim olApt As AppointmentItem

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olCal = olNS.GetDefaultFolder(olFolderCalendar)

For Each olApt In olCal.Items
If olApt.EntryID = ActiveCell.Offset(0, -4) Then
olApt.Delete
End If
Next olApt

[samohtwerdna]

Thanks Killian,

I had already set the olFolder as you specified - Anyway, I went a step further and added a send email form when the approved date is changed that sends a set email to our client from the user - I used your code from post #2 essentially

Sub SendMailAlert(strMessage As String, strTo As String)

Dim olApp As Outlook.Application
Dim olMailItem As MailItem

Set olApp = New Outlook.Application
Set olMailItem = olApp.CreateItem(olMailItem)

With olMailItem
.To = strTo
.Subject = "Your Short Order" & ActiveCell.Offset(0, -4).Value
.Body = strMessage
.Send
End With

Set olMailItem = Nothing
Set olApp = Nothing

End Sub

But for some reason am getting a runtime error #91 "Object or With not Set"
Set olMailItem = olApp.CreateItem(olMailItem)
This is the line it stops on

What am I missing??


Also I want to format the mail message with multiple lines

Private Sub cmdSend_Click()
SendMailAlert "Your order is scheduled for completion in our shop on:" & "" & ActiveCell.Offset(0, 3).Value & "\n" & _
"Delivery Address:" & txtDelAddress.Text & "\n" & "Please verify all the above info and notify me with any changes.", txtEmail.Text
End Sub

Will this work? does the \n equal new line in VBA? :think:

Thanks for the help

[samohtwerdna]

Ok - you might be able to tell that this email stuff in VBA is new to me.

So the vb comand for a new line is &vbCrLf not \n (the other languages I know)

But I still can't figure out why I'm getting the runtime error? I changed the code back to exactly what Killian suggested

Dim olApp As Object
Dim olMailItem As Object

Set olApp = CreateObject("Outlook.Application")
Set olMailItem = olApp.CreateItem(0)

With olMailItem
.To = strTo
.Subject = "Your Short Order #" & ActiveCell.Offset(0, -4).Value
.Body = strMessage
.Send
End With
But this come up with the lame Outlook object model gaurd - Which I dont want

[Killian]

I just tested this and noticed when you use the constant: olMailItem it doesn't have a value despite having correctly set a reference to the Outlook Object model (I my original example, I use "late-binding", so you don't need a reference set, but you have to use the constant values - in this case 0).

Either way, if your PC has a recent version of Outlook or a MS Office security update that includes the Outlook object model guard, you're kind of stuck with it.

Unless of course, you want to circumvent it...

Have a look at this (http://www.vbaexpress.com/forum/showthread.php?t=2700) post for a bit more info