Cancelation Email.

[Homegrownandy]

Recently I've been working to manage outlook calendar from our database. Most of this is now done, however, I have a problem.



When i cancel an appointment from the database. No cancelation email is sent.

I delete the appointment using the following method:

*appointments are made using the "locations name" & "project" & "visit number" so they are all unique.
*all appointments are saved in the database first, then added to the calendar.
*The calendar table is linked to the DB
* name search will find a match and delete this from the calendar.

But i need the Cancelation emails to be sent. I'll explore any sugestion, but what im currently thinking is...

That meeting in the calendar table has a "to" field. It says who its been emailed to. But it only says the outlook short name. Not the full email address.

so it will be like

name1; name2

Now.. I have those names in the database. I also have associated emails to them in the database. But I need some VBA I believe to split the names to look for. So it knows to look for a new name after the ; symbol.

I would then need to find the associated email to those names (i can do this via a query, just unsure how to get that far). Finally i need to send the email adresses a cancelation email containing the job information.

Thats the only way I can see this working.. I may be wrong. In any case I would need some help, I hope I can find some here.

Thanks, Andy.

[mrmmickle1]

You can use a variation of the split function:

Dim myStr As String
Dim mySplit1 As String
Dim mySplit2 As String
Dim mySplit3 As String

myStr = "Matt,John,Jerry"

mySplit1 = Split(myStr,",")(0) '= Matt
mySplit2 = Split(myStr,",")(1)  '= John
mySplit3 = Split(myStr,",")(2)  '= Jerry

Another Tip is to use the Replace function to get the total number of emails...this can help to define the Ubound of your email array

EmailNum = Len(myStr) - Len(Replace(myStr, ",", "")) + 1

[Homegrownandy]

Okay, thanks for the comments there. Really appreciate it.

I dont understand what youre doing with the replace function there. Also.. how would i then use these results in a query?

Im assuming I put the SQL in the VBA and reference each "mysplit"? I know this is easy if you know what youre doing so sorry for asking such basic questions haha.

Then.. Say i get the sql to work in the code... How would i then use the results?

I dont expect you to do this for me.. maybe you could give me a few keywords to google? I have a basic understanding of VBA and if i could find some similar tutorial online i could achieve this. Half the battle is knowing what to look for (that and finding the time).

Thanks again!

[mrmmickle1]

So if you want to cycle through the emails one record at a time you might need to do something like this:

Set rs = CurrentDb.OpenRecordset("SELECT MyTable.Email FROM Contacts")


    rs.MoveFirst 
    Do Until rs.EOF = True
        
        'Save contact name into a variable
        sEmail = rs!Email


        'Move to the next record. Don't  forget to do this.
        rs.MoveNext
    Loop

This issue is that your record has multiple email addresses in it:

There for we will need to parse the email addresses.

So Then this will give us the number of emails we have in the record:

EmailNum = Len(sEmail) - Len(Replace(sEmail, ",", "")) + 1

Then we can put it into a loop:

For intEmail 0 To EmailNum -1

'Email
Email = Split(sEmail, ",")(intEmail)

'Then I would pass the email to a function that will process/send the email
SendEmail(Email)

Next intEmail

if you put all of these together you should be able to accomplish your task.

[Homegrownandy]

Thanks for that, Ill save this to work through.