Blast Emails (2002)

[Leesha]

Hi,

I have an account that would like to be able to send blast/bulk emails via Outlook using the email addresses that are stored in their Access Database. What would be the best approach to setting this up? It needs to be user friendly and needs to be able to stay up to date with the current email addresses as they are always editing the list.

Thanks,
Leesha

[HansV]

Going into the spamming business? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

See the links provided in <post:=457,980>post 457,980</post:>.

[Leesha]

LOL!!! This gal is a total by the rules type of person so you won't find me spamming! This is for a Hospice organization that sends out monthly newsletters etc. They are usinga sales and marketing database that I built them (selling death) and rather than double enter info into Outlook and the database they want to just use the database.

Thanks for the link!
Leesha

[johnhutchison]

Another worry with this activity is your antivirus program.

If you send a lot of emails in a short period of time your virus program can think it is a virus at work. Sometimes you have to turn off scanning of outward mail for a while.

[Leesha]

I mentioned this to them and they are "used" to dealing with it.

Leesha

[Leesha]

I tried this code but get an error stating subject or object not defined and it highlights concat. I made sure my DAO 3.6 is activated and it is.

Dim strRecipients As String
strRecipients = Concat("tblContactNamesBackup", "email")
DoCmd.SendObject BCC:=strRecipients, Subject:="Test e-mail", _
MessageText:="This is a test Hospice's Blast email capability." & _
vbCrLf & vbCrLf & "Hospice of Southeaster CT, Inc.", EditMessage:=True

Leesha

[HansV]

As explained in <post:=455,431>post 455,431</post:>, Concat is a custom function. See the link in that post.

[Leesha]

Daaaaaaaa, somedays I'm just tooooo focused or moving too fast! OK, I've copied the code for the concat function into its own module and am now getting an error message "Unknown message recipient(s), the message was not sent" and the hightlighted area is this code: DoCmd.SendObject BCC:=strRecipients, Subject:="Test e-mail", _
MessageText:="This is a test Hospice's Blast email capability." & _
vbCrLf & vbCrLf & "Hospice of Southeaster CT, Inc.", EditMessage:=True

It may be an internal error on our part as my Outlook is just interoffice mail and not open to the internet. Since "recipients" is not in the address back it my be causing the issue on my end. It's just a guess, but I wanted to mention it so you didn't waste time trying to figure it out. If it's something with the code than I can certainly used your insight.

Also, in the link I noted that you mentioned that the concat function can be used in queries. (another new learning experience!). How would I write the code to do a concat of all the names in a query? I tried:

EMAIL BLAST: CONCAT([EMAIL])

But got an error message stating I'd used the wrong number of arguments.

Thanks,
Leesha

[HansV]

You could insert a line

MsgBox strRecipients

and temporarily comment out the DoCmd.SendObject line. This will enable you to see what the value of the strRecipients variable is.

What kind of query are you trying to create? If you used Concat in a normal select query, it would concatenate all e-mail addresses in each record, which is not what you want, I assume. In a totals query, it could be used to concatenate e-mail addresses within a group.

[Leesha]

WOW!!! The code is correct. The suggestion worked great.

As for the type of query - I started experimenting with a select query. When I changed it to totals query (using group by ) I get the same error. This is the query SQL:

SELECT CONCAT([EMAIL]) AS [EMAIL BLAST]
FROM tblContactNames
GROUP BY CONCAT([EMAIL])
HAVING (((CONCAT([EMAIL])) Is Not Null));

I'm getting the error that there is the wrong number of arguments.

Leesha

[HansV]

As explained in the text file with the Concat function, it expects at least two arguments: the name of the table and the name of the field to be concatenated. Try this SQL:
<code>
SELECT Concat("tblContactNames","EMAIL") AS [EMAIL BLAST]
</code>
This query is not based on a table; instead, the Concat function retrieves data from the tblContactNames table.

[Leesha]

That works fine. My problem is how do I filter out null records. AND how would I sort by certain types of records. For example in some instatnces they may only want the send email to former patients, in other instances it may be nursing homes etc. Is there a way for me to filter for this in a query?

[HansV]

As explained in the text file with the Concat function, Concat has a third argument that can be used to specify a condition. For example, to filter out blank e-mail addresses:
<code>
SELECT Concat("tblContactNames","EMAIL","EMAIL Is Not Null") AS [EMAIL BLAST]
</code>
and if you want to add another condition:
<code>
SELECT Concat("tblContactNames","EMAIL","EMAIL Is Not Null AND GroupID = 3") AS [EMAIL BLAST]
</code>
to select only records for which GroupID equals 3. Of course, you must modify this to suit your needs. And if you use a text value, put single quotes around it:
<code>
SELECT Concat("tblContactNames","EMAIL","EMAIL Is Not Null AND Group = 'Research Nurses'") AS [EMAIL BLAST]
</code>

[Leesha]

I missed that in the code as I copied and pasted it without thoroughly digesting it to learn it / understand it (too many people asking me questions to allow me to focus unfortunately).

I experiemented (trying to think like Han's) to see if I could substitute a query for tblContactNames. First I created qryBlastEmail 1 which filters for null as well as will filter for whatever group the organization chooses if they want to narrow down the selection. I then used the following code and the result looked correct.

SELECT Concat("qryemailblast 1","EMAIL") AS [EMAIL BLAST]

Am I being nieve or is this also a way to do it?

Thanks for all the help!!

Leesha

[HansV]

That is also a very good way to do it. Congrats! Experimenting is the best way to find out things.