Sending a Outlook Greeting Card using MS Access 2

[istone]

Hi All,
First of all Happy Holidays.
We usually do this piece manually. I want to find out if it's possible to send a Greeting card (Outlook card) to all our clients using their email address that we have in our database. The goal will be to create a button in a form and once clicked on it'll send the same card to all the clients using their email address we have in the table.
Thank you in advance

 

[istone]

Private Sub Command0_Click()

Dim db As Database
Dim rs As DAO.Recordset
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email FROM Table1")


Do Until rs.EOF
On Error GoTo ErrorHandler

With objEmail
.To = rs!Email
.Subject = "Happy Holidays"
.HTMLBody = "Greetings from Chernoff Diamond<br><br>"
.Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"

'.Send
'Use Display rather than send when testing
.Send
End With
ErrorHandler:

Resume Next

rs.MoveNext

Loop


End Sub

 

[GingerR]

You have to loop thru the email address and build a string like

Dave.Smith@MS.com, Datagrrrl@hotmail.com, etc

You are just putting in one email address and sending one email at a time.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[istone]

I am not sure I am following you. I have a table with 206 emails.

 

[GingerR]

Your original post says

The goal will be to create a button in a form and once clicked on it'll send the same card to all the clients using their email address we have in the table.

so you said you wanted the TO: line to have all the emails listed, correct? Then in a newer post you say

and then I click end and the email is sent but to only one person the first on the table list

and i'm saying this is because you are only putting one email address in the TO: portion of the Email object. You have to loop thru the email addresses and put them in a string, one after another, separated by commas or semi-colons. Surely there's a ton of examples if you search.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[istone]

The word end is the one that appears when there is an error message. When an error message comes up it shows 2 buttons one says: end and the other say debug. I click on end to stop the error.

The .To = rs!Email refers to the field in the table. This field has all the email we want to send the card to.

 

[GingerR]

And this error handler statement is in the wrong place too:

ErrorHandler:
    
    Resume Next

should be just above End Sub




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[istone]

thank you

 

[Remou]

GingerR
I do not think that it is necessary to list all the emails on the To line. It may be more suitable to send individual emails. I would agree with your second suggestion of moving the error trap.

istone
I would test without the error handler. I would not use resume next in an error handler. You must check that the email address exist.

'Email address is null or zero length string
If Trim(rs!Email & "")="" 
    'Do stuff
Else
    'Send email

... and so on

 

[istone]

When I remove the error handler, I get a message saying: the file has been removed or deleted.
The email is sent but to only one person.
strange..

 

[Remou]

Please post your code showing the latest changes.

 

[istone]

Remou thanks so much for staying with me on this issue...

here is the code minus the last if statement you suggested:

Private Sub Command0_Click()
Dim db As Database
Dim rs As DAO.Recordset
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email FROM Table1")


Do Until rs.EOF


With objEmail
.To = rs!Email
.Subject = "Happy Holidays"
.HTMLBody = "Greetings from Chernoff Diamond<br><br>"
.Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"


.Send
End With

rs.MoveNext

Loop


End Sub

 

[Remou]

Which line causes the problem?

Do you know how to step through code?

 

[istone]

this line: .To = rs!Email

 

[Remou]

Private Sub Command0_Click()
Dim db As Database
Dim rs As DAO.Recordset
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
'Only select records with an email address
Set rs = db.OpenRecordset("SELECT Email FROM Table1 WHERE Trim(Email & '')<>''")


Do Until rs.EOF

With objEmail
  .To = rs!Email
  .Subject = "Happy Holidays"
  .HTMLBody = "Greetings from Chernoff Diamond<br><br>"
  .Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"

 
  .Send
End With

rs.MoveNext

Loop


End Sub

 

[GingerR]

This puts all email addresses into the TO: line. I tested it and it works fine.

Dim db As Database
Dim rs As DAO.Recordset
Dim strTO As String
'Dont't forget to reference the Outlook Object Library
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Set db = CurrentDb
'Only select records with an email address
Set rs = db.OpenRecordset("SELECT Email FROM Table1 WHERE Trim(Email & '')<>''")



Do Until rs.EOF

    If strTO = "" Then
        strTO = rs!Email
    Else
        strTO = strTO & "; " & rs!Email
    End If

    rs.MoveNext
Loop

With objEmail
  .To = strTO
  .Subject = "Happy Holidays"
  .HTMLBody = "Greetings from Chernoff Diamond<br><br>"
  .Attachments.Add "P:\Greetings.jpg", olByValue, , "Stuff"

    .display
  '.Send
End With

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PaulBricker]

Not to try to add to the solution but Ginger, if you use your method all the email addresses will appear to all the receipients and as a general business rule you don't want to do that. Remou's loop creates the email and sends it before building the next one.
Also, if I was doing this, I would build a query that checked all the email addresses, got rid of nulls and did any trimming, etc before I ran it through the code. It just simplifies everything a bit.

Paul

 

[istone]

GingerR, you did it. It works. Thank you so much. And I also want to thank everyone else that took the time to help me. Happy holidays

 

[istone]

I am back again. I have a small change to make. When I send the email it shows everyone address on TO: I was asked to modify the code to hide all the addresses from the TO. is it possible?
the person who recieve the email will see:

From: Istone@Y.com
To:
CC:
Subject: Happy Holidays


thanks in advance

 

[Remou]

Perhaps you should read Paul Bricker's comment above?

 

[GingerR]

Yes I know it works, I've been trying to tell you for days to loop thru all the addresses and put them on one line!! You could get a lot from reading thru people's posts here, as well as searching the forum, and piecing together various bits of advice!!

As for "hiding" the addresses, just put them in the BCC (BLIND CARBON COPY) line instead.

Just change the .TO = strTO line to

.BCC = strTO

And Paul, I agree with everything you've said...I was trying to not give the whole fish as all the advice in the post is valid and should be followed...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html