Email VBA

[dbn00bz]

Hi,

I have two tables;

tblContacts

300 rows,
4 columns; Name, UNIQUE_ID, Email_Address, Send_email
​

tblData

18000 rows,
Various columns of data including UNIQUE_ID

​

Note:
UNIQUE_ID can have multiple records in tblData, but only 1 record in tblContacts


I am trying to write some vba to;

Where tblContacts.Send_Email = Yes,
Filter tblData to records containing the UNIQUE_ID from tblContacts
Email that data as an xlsx format to the tblContacts.Email_Address

Loop for the 300 records in tblContacts


I've come up with some VBA to send the whole table to myself which works, but I can't work out how to do the filtering bit;

Sub testemail()
DoCmd.SendObject acSendTable, "tblFINAL_DATA_TO_SEND", acFormatXLSX, _
"dbn00bz@dbn00bz.com", , , _
"Test Email Data", , False
End Sub

Any help or advice, greatly recieved.

Thanks,
DbN00bz

 

[Wolff]

Can you not write a query to do the filtering, then send the query results?

 

[dbn00bz]

If that's what you think is the best way of doing it... (I'm really not sure of the best way)

It's the filtering bit I'm having trouble with, how do I create a query that filters to each individual row in the contacts table without having to write 300 queries?

 

[dbn00bz]

Maybe something like...

Private Sub NewTestEmailSend()
'Open My Contacts Table and Filter to those that require an email
DoCmd.OpenTable "tblContacts", acNormal, acEdit
DoCmd.ApplyFilter "", "[tblContacts]![Send_Email]= -1"

Not sure what needs to go here

'Open the data table and filter to the relevant lines above
DoCmd.OpenTable "tblData", acNormal, acEdit
DoCmd.ApplyFilter "", "[tblData]![unique_id]=""[tblContacts]![unique_id]"""

Not sure what needs to go here

DoCmd.SendObject acSendTable, "tblData", acFormatXLSX, _
"[tblContacts]![email addresses]", , , _
"Subject Line for Email", , False

Then Loop...

End Sub


Knowing my Luck, it's nothing like what I need, and I've probably wasted an hour googling...

Cheers....


DBn00bZ

 

[Lightwave]

Hey there DB

Ok well I can show you some code that hopefully shows you how you can set up a recordset and loop through that to create e-mails to the separate individuals. This wouldn't completly be the solution because even though you would replace your DoCmd.SendObjects line for mine you would only be sending the same file to each of your contacts.

I would say first off get the following code working imagining that your going to send the same info to each contact but not as a group mail individually.

I've come back to this and realised that you could create the query first as a pivot table concatenating the information that you are interested in see second post and reference that in the code below

'Set up the references for the recordset
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset

'Set up the string that will hold all the e-mail addresses
Dim sToName As String

'Identify the database location
Set MyDB = OpenDatabase("C:\TestBed" & "\Sport.mdb")

'Identify the table within the database - I think you would put your tbl contacts in here
'Note the advice above would be to refer to a query which you have defined using the query builder which has the records you are interested in
'which case you would replace tblcontacts with for example qrycontacts
Set rsEmail = MyDB.OpenRecordset("tblcontacts", dbOpenSnapshot)

'now the loop
'remember the [] represents a field name relating to field of all the separate records you wish to put together
'from your notes think that is [e-mail addresses]

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![e-mail addresses]) = False Then
sToName = rsEmail![e-mail addresses]
DoCmd.SendObject , , , sToName, , , "Test", "Message Body", True
End If
.MoveNext
Loop
End With

Set MyDB = Nothing
Set rsEmail = Nothing

 

[Lightwave]

Just thought about it and what I think you could do is this

Create a query a pivot table that has each person as a single line and on the end all the fields relating to the data unique to that individual.

Work out how to concatenate those fields , fairly simple to do this in expression builder and get them into a single field. You could then set up a string variable and relate it to the the field containing this unique information. Now within the loop created above substitue the "message body" attribute of SendObjects with the variable referenced to this concatenated field. Now when the the routine loops through the records provided that concatenated field is in the same query as the e-mails on the same line my theory is that it will pick up that information and put it into the message body.

Note its unlikely to format it into xslx probably just be a textual string..

If its urgent you could concatenate it in Excel re-import either with the pivoting happening in access or excel it into access and then hey presto the table will be ready to go

Bit dirty but would be a good way to learn

 

[dbn00bz]

Hi Lightwave,

Thanks for those.

I'm had a go at using the bit of code that you sent over, above, and I'm getting a runtime error saying;

The database has been placed in a state by user 'Admin' on machine..... that prevents it from being opened or locked.

The debug brings up;

"Set MyDB = OpenDatabase("M:\Mailer.accdb")" in yellow.


I'm going to have a shot at writing it in a query like you mentioned above.

Although it's interesting learning all these bits, it's the most frustrating thing in the world!

Cheers,
DBn00bZ

 

[Lightwave]

Ok try this

I get that message in 2003 when I've just edited the code and saved it then try to run the code.

In the first instance try exiting either the form or just to be sure exiting the application and going back to the particular form and running it. On exit or on exit of application in Access 2003 the locking seems to be reset / unlocked on my version.