Which line errors?
Value is a reserved word. Should not use reserved words as names for variables or objects.
Should include Option Explicit at top of every module. VBA editor can be set to do this automatically when module is created.
Which line errors?
Value is a reserved word. Should not use reserved words as names for variables or objects.
Should include Option Explicit at top of every module. VBA editor can be set to do this automatically when module is created.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Can you try this?
Cheers,Code:Dim sFileName As String, sEmail As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) On Error Resume Next rst.MoveFirst Dim i As Integer For i = 1 To 10 'sending 10 emails individually sEmail = rst("Email") 'REPLACE WITH YOUR FIELD NAME sFileName = "W:\My Documents\Customers.txt" Call vcSendEmail_Outlook("Your email subject", sEmail, , , sFileName, "") rst.MoveNext Next i rst.Close Set rst = Nothing
Can you try this?
Cheers,Code:Dim sFileName As String, sEmail As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) On Error Resume Next rst.MoveFirst Dim i As Integer For i = 1 To 10 'sending 10 emails individually sEmail = rst("Email") 'REPLACE WITH YOUR FIELD NAME sFileName = "W:\My Documents\Customers.txt" Call vcSendEmail_Outlook("Your email subject", sEmail, , , sFileName, "") rst.MoveNext Next i rst.Close Set rst = Nothing
--------I've made some modification and code below is working fine for following scenarios
1) sending multiple emails (I've setup 2 for testing purposes)
2) extracting multiple email addresses from the table and putting it in BCC ( thanks for the suggestion. it's a good option)
----------what is not working or remaining
1) when I run the code it creates an email with the relevant data populated but then I am required to click "Send" button. How can I send the email without interruption
2) my outlook currently has 2 accounts added to it. for some reason, whenever new email is created, it uses a non-default email address requiring manual change. how to always use a fixed "From" email address.
3) is there a way to give a pause (lets say for a minute) between the emails
Thanks again for all your assistance
Code:Option Compare Database Sub Command0_Click() Dim sFileName As String, sEmail As String, BccEmail As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name On Error Resume Next rst.MoveFirst Dim i As Integer Dim x As Integer For i = 1 To 2 'sending 2 emails individually sEmail = rst("Email_Address") 'Field Name CCEmail = sEmail BccEmail = "" For x = 1 To 5 BccEmail = BccEmail & "," & sEmail rst.MoveNext sEmail = rst("Email_Address") Next x Call vcSendEmail_Outlook_With_Attachment("Subject", "to@info.com", "", BccEmail, "W:\My Documents\Customers.txt", "") Next i rst.Close Set rst = Nothing End Sub
For 1)
Change
OutMail.Display
to
OutMail.Send
For 2) what do you mean by 'non-default', it should use account for user logged in to computer
For 3) why?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
One way to do #3:
http://www.theaccessweb.com/api/api0021.htm
--------I've made some modification and code below is working fine for following scenarios
1) sending multiple emails (I've setup 2 for testing purposes)
2) extracting multiple email addresses from the table and putting it in BCC ( thanks for the suggestion. it's a good option)
----------what is not working or remaining
1) when I run the code it creates an email with the relevant data populated but then I am required to click "Send" button. How can I send the email without interruption
2) my outlook currently has 2 accounts added to it. for some reason, whenever new email is created, it uses a non-default email address requiring manual change. how to always use a fixed "From" email address.
3) is there a way to give a pause (lets say for a minute) between the emails
Thanks again for all your assistance
Code:Option Compare Database Sub Command0_Click() Dim sFileName As String, sEmail As String, BccEmail As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name On Error Resume Next rst.MoveFirst Dim i As Integer Dim x As Integer For i = 1 To 2 'sending 2 emails individually sEmail = rst("Email_Address") 'Field Name CCEmail = sEmail BccEmail = "" For x = 1 To 5 BccEmail = BccEmail & "," & sEmail rst.MoveNext sEmail = rst("Email_Address") Next x Call vcSendEmail_Outlook_With_Attachment("Subject", "to@info.com", "", BccEmail, "W:\My Documents\Customers.txt", "") Next i rst.Close Set rst = Nothing End Sub
Re #3. Let’s say, I have setup a yahoo and a Gmail account through the outlook and Gmail is setup as the primary account. When I send the emails through vba, I want to use gmail. But in my case yahoo is always set as a default when new email is created.
Thanks
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
The only problem with this approach is that it freezes my screen during the pause. Here is the code that I have used. Please advise
Code:Option Compare Database Private Declare Sub sapiSleep Lib "kernel32" _ Alias "Sleep" _ (ByVal dwMilliseconds As Long) Sub sSleep(lngMilliSec As Long) If lngMilliSec > 0 Then Call sapiSleep(lngMilliSec) End If End Sub
The only problem with this approach is that it freezes my screen during the pause. Here is the code that I have used. Please advise
Code:Option Compare Database Private Declare Sub sapiSleep Lib "kernel32" _ Alias "Sleep" _ (ByVal dwMilliseconds As Long) Sub sSleep(lngMilliSec As Long) If lngMilliSec > 0 Then Call sapiSleep(lngMilliSec) End If End Sub
I have found the solution...
Code:Sub Wait_Time() Dim time1, time2 time1 = Now time2 = Now + TimeValue("0:07:30") Do Until time1 >= time2 DoEvents time1 = Now() Loop End Sub
There is only one challenge remaining. - Following code/logic is good if I have 50 records or more.
- for the scenarios, < 50 email records, it repeats the last email address from the table until reaches the count of 50.
as an example, let's say I have 43 email address, following the logic below,
- 8th email will have BCC email addresses as follows: #35, #36, #37, #38, #39, #40 (no issues with this one)
- 9th email will have BCC email Addresses as follows: #40, #41, #42, #43, #43 (43 is repeated twice)
- 10th email will have BCC email address as follows: #43, #43, #43, #43, #43 (43 is repeated 5 times)
How can I modify the code to exit the loop if end of the table is reached
Code:Sub Command0_Click() Dim sFileName As String, sEmail As String, BccEmail As String, eBody As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name eBody = "<HTML><BODY>Message</BODY></HTML>" On Error Resume Next rst.MoveFirst Dim i As Integer Dim x As Integer For i = 1 To 10 'sending emails individually sEmail = rst("Email_Address") 'Field Name CCEmail = sEmail BccEmail = "" For x = 1 To 5 'Adding 5 email address to BCC BccEmail = BccEmail & ";" & sEmail rst.MoveNext sEmail = rst("Email_Address") Next x Call vcSendEmail_Outlook_With_Attachment("Subject", "To@email.com", "", BccEmail, "W:\My Documents\Customers.txt", eBody) Call Wait_Time Next i rst.Close Set rst = Nothing End Sub
Thanks again for your assistance
You can use rst.EOF to check if you are at the last record and if yes exit loop.
Cheers,