send emails to addresses from table 1

[patrichek]

Hi,
I need ideas on how to auto send a form letter to contacts that show up in a temp table i made based on a query.

Basically if we haven't received a certain report that our contacts promised to send us they will show up in this table, and from there i would like to auto generate emails to these particular contacts to remind them. Is it possible to automate this?

Could I use a report or should i go with mail merge? any other ideas are definitely welcomed.

Thanks!

 

[lameid]

You will save a lot of trouble if you use a mail merge.
If you want to totally automate it, I would record a macro in Word that opens the document and completes the merge. You should be able to use that code in automation from Access.

You might have a lot better luck searching the "Microsoft: Access Modules (VBA Coding)" forum.

 

[patrichek]

Hi,
actually i used this code:

Private Sub cmdSend_Click()
    
  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 EmailName " & _
             "FROM mytbl"
    
    Set rst = dbs.OpenRecordset(strSQL)
    
    strEmailname = ""
Do While Not rst.EOF
      strEmailname = strEmailname & rst("Emailname") & ";"
      rst.MoveNext
Loop
      strEmail = Left(strEmailname, Len(strEmailname) - 1)
      strbody = vbCrLf & vbCrLf & _
                "put the text you want in your email here"

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

    Set dbs = Nothing
    Set rst = Nothing
End Sub

i found some of it on this site in the vba for applications forum and customized the other part.

thanks!

 

[JerryKlmns]

patrichek,

When you are done whith a recordset first close it and then destroy it. The next step is to destroy the command object that you executed for your recordset (in case you did use one) and then destroy it. The final step is to close the ADO connection or DAO database and then destroy it. If you used an object resembling the ADO CurrentProject.Connection or DAO CurrentDB, don't close it, just destroy it. In this case:

Sub cmdSend_Click()
...
...
...
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

 

[patrichek]

thank you!

 

[patrichek]

looks like i'm limited to the amount of text i can put in the body of the email, is this correct? I'm only able to put one line here:

strEmail = Left(strEmailname, Len(strEmailname) - 1)
      strbody = vbCrLf & vbCrLf & _
                "put the text you want in your email here"

thanks!

 

[lameid]

Actually you can put a lenghty message in...


strbody = vbCrLf & vbCrLf & _
"put the text you want in your email here"

Is putting two 'Enter' keys (Carriage Return and Line Feed) in the text at the begining (vbCRLF constant). You could do something like the following instead.

strbody = "Line 1" & vbCrLf & "Line2"

Then your e-mail would look like:

Line1
Line2

 

[patrichek]

sry, i'm a newbie! so i would have to put strbody =the amount of lines in my text?

 

[lameid]

No, you are concatenating a string together (building text) and putting that in...

vbCRLF is a constant that is equivalent to a Carriage Return and line feed (same as pushing Enter in Word)

The double quote (") is a text delimiter. It says anything between the two of them is text.

& is the contatenation operator... Just as 4 + 3 = 7; "Some text " & "Some More Text" = "Some text Some More Text"

You need a vbCRLF to seperate each line of text. In my example I used the text "Line 1" for the first line and "Line 2" for the second line.

 

[patrichek]

thank you! STAR!

 

[patrichek]

does anyone know how to copy from a textfile using code? ie...get external txt file?

 

[JerryKlmns]

patrichek,

Many posts on that issue on forum705. Search there. And better post at that thread too