Sending email to several contacts 1

[marcoITALY]

Hi.
I use a query populating a form, with a set of records (from a sort of telephone directory archive).
basically, I make a search for names, I get some records with email addresses.
I do not know how to write the VBA code to maka access send an email message (doCmd.SendObject) with all email addresses of all records obtained by the query at once.

can you help me maybe?
thanks
Marco, italy.

 

[LogicalmanUS]

marcoITALY,

I can't be certain on this, but I do know that you may concatonate email addresses by using a semi-colon ( as a separator.
That being the case, then use a string variable to add all the email addresses together and then this string becomes the 'sendTo' parameter value.

Logicalman

 

[marcoITALY]

Thanks Logicalman, this is exactly what I meant.
the problem is, I am not an expert of VBA, and i do not know the correct sintax to use....

Thanks anyway for the advice.

cheers
Marco

 

[LogicalmanUS]

Marco,

No problem, post the code you have to retrieve the records of the emails, and I will do my bext to assist you in writing the string concatonation code.

Logicalman

 

[deharris2003]

Marco,
here is a basic function that will scroll thru a table and send emails to all the individuals listed in that table. it can be easily modified to send emails to only certain people.

Function SendMail()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strEmail As String
Dim strbody As String

' Return reference to current database.
Set dbs = CurrentDb
'Find record to send report of
strSQL = "SELECT Name, Email " & _
"FROM Email"

Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF

strEmail = rst("Email&quot
strbody = vbCrLf & vbCrLf & _
"put the text you want in your email here"

' send email.
DoCmd.SendObject acSendNoObject, "", acFormatTXT, strEmail, , , strHP, strbody, False

rst.MoveNext
Loop
Set dbs = Nothing
Set rst = Nothing
End Function


hope this helps

 

[LogicalmanUS]

deharris2003,

Excellent code, but it creates mutiple emails. It can be changed to send a single email to multiple recipients:

.....

strEmail = ""
Do While Not rst.EOF
strEmail = strEmail & rst("Email&quot & ";"
rst.MoveNext
Loop
strEmail = left(strEmail,len(strEmail)-1)
strbody = vbCrLf & vbCrLf & _
"put the text you want in your email here"

' send email.
DoCmd.SendObject acSendNoObject, "", acFormatTXT, strEmail, , , strHP, strbody, False

......

just by concatonating the address string and moving the sendmail command out of the loop.
(Changed syntax is in red and additional syntax is bolded).

Logicalman

 

[deharris2003]

LogicalMan,
I see your point. with your modifications it should also speed up processing time.

 

[LogicalmanUS]

deharris2003,

Sorry, I didn't mean to steal your thunder, it's just I've been through this before on a number of webs we've written. Your code works beautifully for single addresses in any case.

Thanks,

Logicalman

 

[marcoITALY]

WOW!! COOl!!
I would not expect so many follow ups in such a short time... thanks.

The email addresses I want to get, come from a query (quite a huge one,actually!) searching for data within this table with some 2000 directories, with many fields each (4 contacts, different tel numbers, addresses etc,products,etc). Therefore, either I copy and paste the sql code of the query(some 10 lines) , next to the "strSQL" value, or I must find a way to get the values not from the query, but from the form itself, using a counter for each record shown. but maybe this is too complicate.
what do you think?
thanks again to all.
marco

ps: just to clarify, as a matter of facts, the form I am talking about is the one popping up once the query is run, and showing the matching records (name, town, category, email). this is the form I want to add the "send email to all" feature.

 

[rapidapp]

What component/reference in VB6 needs to be enabled to allow DoCmd.SendObject to allow emails to be sent? I cut an pasted the code sample but can't figure out what DLL it is in.

Thanks

 

[LogicalmanUS]

Marco,

You can use the recordsetclone object based on the recordsource object of the form. The basic advantage is that it makes a copy of the records rather than the actual source:


Dim rst As Recordset
Set rst = Me.RecordsetClone


At this point you can use the new recordset object (rst) just as if you had opened it from the original sql statement.

The rest of the code would be the same.

Logicalman

 

[deharris2003]

rapidapp,
I am not certain, what references would be needed in VB6. But I know in VBA all you should need is a reference to the Visual Basic for Applications and it is possible that you may also need to reference the Microsoft Active X data objects 2.1

Hope this helps

 

[deharris2003]

LogicManUS,
No problem man. That is what Tek-Tips is all about people helping people. Besides I am always open to improving my skills in programming.

 

[psemianonymous]

If you're actually doing this in a VB6 project, you'll need a reference to "Microsoft Access (version number)". For mine, Access 97, it's version 8.0. You also need an Access application open so that it can run the "DoCmd" command. I'm not too hot on Automation stuff, but the code below is along the lines of what you'll need.

DoCmd unfortunately isn't a command that you can just link to. It runs only inside Access, so you have to ... you have to basically open up access, then open the MDB from the new access application, then run the DoCmd from there. I say you'd be better off finding a non-Access solution--there are other ways to send emails without using DoCmd. That, or just run the code from within Access itself instead of using all the complicated Automation code.

Public Sub docmds()
    Dim oApp As Access.Application
    
    Set oApp = New Access.Application
    oApp.OpenCurrentDatabase "yourdb.mdb"
    oApp.DoCmd.SendObject 'etc
    
    Set oApp = Nothing
End Sub

Peter

 

[deharris2003]

hey rapidapp,

here is something that might work better for you in VB6.

Function Sendmail()

'Declare Variables
Dim strESubject, strOutPut, strTo, strFrom As String
Dim mail As CDONTS.NewMail
Set mail = New CDONTS.NewMail

strTo = "someone@somewhere.com"
strFrom = "someone@somewhere.com"
strESubject = "Put your subject here"
strOutPut = "Put your email message here"

'Send Email
mail.From = strFrom
mail.To = strTo
mail.Subject = strESubject
mail.body = strOutPut
mail.Send

End Function