compose outlook email - bcc twist and don't send!

[misscrf]

I have looked around at some posts, but I have a specialized request from a user.

I have a database that I set up for a user. Our company offers a quick lube service. A guy comes to our office, and people can sign up to get an oil change, tire rotation, state inspection, etc, while they work.

Now we want to have this database have a button on the main menu. It would be called "Reminder Email"

The user would hit that and get a form that would ask them - how many month reminder do you want to send (2 month, 3, 4, etc.)

Then it will query the customers, for how often they get a reminder.

If she chooses 3 months, then it will query those set for a 3 month reminder, and whose last date was 3 months.

Then it will compose an email to the user (always the same email) and each customer that was found / hit in the query, will have their email in the bcc. The subject and body will be all set.

I do not want it to automatically send though. I want it to stop right there.

1) run the query.

2) put the query result emails in the bcc

3) but the never changing To email, subject, and body in their places

4) leave the email on top.

The user can then check it over, and hit send once happy.

Can someone help me with this? I have just never used Access to compose email before. All of the posts and FAQs seem to either send the email without a chance for the user to check it, (add something else if they want to) or it sends 1 email to each email result from the query.

Any help on this customized way of doing this would be great.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Instead of the .Send method use the .Display one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

This:
Access 2002 Email Form Record
thread181-812267
Has a sample similar to your app. Note the .Display
You will find lots of email stuff in these fora, check for the keywords you need, that is Display, BCC etc.

 

[misscrf]

Thanks for both of your responses. I have looked at the FAQ's and I have started some of the input set up. This is for the user to check if the customer should get a reminder. If yes, then show the reminder months combo, once they pick how many months, the reminder date pops up and fills in with the dateadd function.


Now, at the point where I have some of those filled in.

My next step is to create a form.

First, I decided I will just send 1 email to each person, and don't need to edit it. I just want to set a certain email to be sent for a certain reminder. If it is for an oil change, it will say "time for oil change" , if inspection "time for inspection".

So, the main menu would have a button to send reminders.
The user clicks it, and it opens a form with buttons for what type of reminder.

They choose, ie, oil change reminder. It opens a form with a field for the next date that the service will be available, and a button to "send reminder". If the reminder date on any person's record is before or = to the service date, then they get a reminder. The trick is it would be before or = to that date, but after the last service date. (so we don't remind people that got an oil change 5 months ago) Im not sure how to contruct that statement completely.

From there, I would just say, if that is all true, then send email, else - "no emails to send".

Does that sound right?

Can anyone help me with the statement that I need to build to evaluate who gets what reminder?

Here is a url to a pic of my entry form with subform. Hopefully that might give you a visual of what we import, which gives us the info to decide who gets what reminders, and when:

http://www.geocities.com/misscrf/carschedform.jpg

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Remou]

I will ramble for a few lines. Say you have a service table:
[tt]ServiceID Description TextForEmail ServiceDate
1 Oil Change D'you want an OC? 01/01/06[/tt]

And you want to gather in anyone with a service in or around that date:

Dim rs As DAO.Recordset
dteOilDate = DLookup("ServiceDate", tblServices, "ServiceID = 1")

strSQL = "SELECT  * FROM tblCutomerServices " _
      & "WHERE ServiceType = 1 And ServiceDate Between #" _
      & dteOilDate - 2 & "# And #" & dteOilDate + 2 & "#"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF()
    'Send Email
    rs.MoveNext
Loop

You could also cycle through the service table.

 

[misscrf]

Sorry I have not replied. I got pnuemonia and this got put on the back burner. I will look at your suggestion and get back to this post on where I am at. I appreciate the post.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Remou]

I hope you are feeling much better.

 

[misscrf]

I am thanks, and now I am getting back to this. Looking at this code, I am starting to rethink what I want to do.

This is set up so that 1 user maintains the db. She inputs the people, their appointments and reminders.

What I would like to do, is add a menu button to "Process reminders"

Then she would choose what type of reminder (ie oil change or inspection or other) and then the date of the next service.

Then she would hit process.

This would

+ choose the appropriate email - oil change reminder email, etc.

+ Look through the schedule table for oil change reminders

+ query them to the service date as time for a reminder.

The tricky thing would be to make sure that I am not giving someone a reminder for an oil change because they have a reminder from a year ago.

Make sense?

Because of the setup, a person will accumulate old reminders. I only want to go by the most recent, so I need to account for that in the query. I think, after that, I can just send the email.

The user has said that she doesn't need to see it before it sends.

Thanks for any help.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I tried this code that I found online:

(

http://www.datastrat.com/Code/MultipleEmail.txt)

But I had some issues. I believe that I am under some constraints, because I have Outlook running on Exchange.

From my research, I think I would like to do the following:

I have a table with employees, which includes their email. I have a table with scheduled appointments for car services (oil changes, inspections, etc) it is something our company offers.

This schedule table contains a checkbox for if the service should have a followup reminder. Then it has a field for months - how many months from this service date, the reminder should be based on the months. Finally is a field that calculates the reminder date.

What I want to do is have a form for processing reminders. The User would put in the date of the upcoming service, and click a "process reminders" button. It would run a query to find the (LAST) reminder date for an employee, where the reminder date is >= the service date. It would then send an email to that person - 1 email to each person (ie there may be 10 results, but that would make 10 emails go out, 1 to each person) Each email would say in the subject "This is your reminder for your "&[service type]

The body would say "Please reply to schedule an appointment, for the next service date, on "& [form date entered by user]&" Thank you, Sender"

Here is how I tried to modify the code from above, but from trying to debug it, now it doesn't do anything.

Function Email(strTo As String, strSubject _
        As String, Optional varMsg As Variant, Optional varAttachment As Variant)

' ©Arvin Meyer 1999-2004
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email

'Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim strTo As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
'Dim objEml As Outlook.MailItem
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailReminder")
Set objOutl = CreateObject("Outlook.application")
'Set objEml = objOutl.createItem(olMailitem)

If IsNull(Me.cmbReminder) Or IsNull(Me.cmbServiceDate) Then
DoCmd.CancelEvent
MsgBox "You must choose a type of reminder and a service date!", vbCritical, "Please Choose Your Reminder"
Else

With rst
    If .RecordCount > 0 Then
        .MoveLast
        .MoveFirst
        Else
        MsgBox "No Reminders to Send!", vbOKOnly, "Thank you"
    End If
End With

For i = 1 To rst.RecordCount
    If Len(rst!EmailAddress) > 0 Then
        strTo = "cfay@harrisbeach.com"
        strBCC = rst!EmailAddress
    Dim objEml As Outlook.MailItem
    Set objEml = objOutl.createItem(olMailitem)

        With objEml
            .To = strTo

            .Subject = "Your car is due for an oil change!"
            
            .Body = "Please reply to schedule your oil change for the next service date, which will take place one " & cmbServiceDate
           

' Uncomment for attachment
'            If Not IsMissing(varAttachment) Then
'                .Attachments.Add varAttachment
'            End If
            
            .Send
        End With
    End If
    Set objEml = Nothing
    rst.MoveNext
Next i

ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing
End If

    Exit Function
    
Errhandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere

End Function

Any help would be appreciated. I think part of the problem is that while I can do some code, I don't understand declaring objects very well, so a lot of this code doesn't make sense to me.

Thanks.


misscrf

It is never too late to become what you could have been ~ George Eliot