UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Use A Form Contact Source And Send An Email To Each, Access 2016    
 
   
wizkid_23
post Jul 12 2018, 12:39 AM
Post#1



Posts: 2
Joined: 12-July 18



Hey guys,

First of all thank you for reading my post and for your attempt at helping.
I'm stuck on a project that involves the following:

I have a one main form which contains core information about a contact.
The data elements in the main form are :
a) Name
b) Phone Number
c) Email Address
d) Address
etc.

There are 4 sub-forms attached to the main form.
Each of the sub-forms have a different data source.

Within the main form, I have two buttons:
The first button exports EACH sub-form into an excel file and saves them individually into a folder in "C:\" drive when there is data on those data sources
The second button opens an email in Outlook with the number of attachments saved on the "C:\" drive above

Here is where I need your help!

The export and the "send" button are working great.
However, I don't want to sit there and click on that export + send button 5K times.

How do I use my "form" to loop through the records, execute the codes above and send an email to EACH of my contacts without my manual clicking?

THANK YOU for taking the time to read my post ... BTW this is my first post in UtterAccess!






Go to the top of the page
 
JonSmith
post Jul 12 2018, 02:27 AM
Post#2



Posts: 3,956
Joined: 19-October 10



Yes, there are two kinds of loop (as far as I know) available to you in VBA a DO LOOP and a FOR NEXT


I think the Do loop sounds good in this case.
Its hard to write some code to demonstrate it too much as it depends abit on your existing code. If, for example its using the controls on the form to populate the reports then you'll need to iterate through all the records on the form. That wouldn't be my personal preference as instead I'd open a recordset in VBA and loop through that (less on screen mess).

Either way, thats a start for you. Tell us what more information you need and we'll continue to try and fill in the gaps.
Go to the top of the page
 
projecttoday
post Jul 12 2018, 05:06 AM
Post#3


UtterAccess VIP
Posts: 10,248
Joined: 10-February 04
From: South Charleston, WV


welcome2UA.gif

One point you need to clarify: is there anything different about each email or are they all exactly the same?

I would use an unbound form with a button to launch the emails with recordset code based on the table to loop through the table and execute either Outlook object code or Docmd.SendObject.

I've never done 5,000 at a shot myself.
Go to the top of the page
 
wizkid_23
post Jul 19 2018, 10:57 PM
Post#4



Posts: 2
Joined: 12-July 18



Here is my code that is working well when you click on the button "Send"

I need the code below to execute for each parent record on the form.
Once the code has execute and the actions have completed, i need it to call the "DoCmd.GoToRecord , , acNext"
and start the process over until it loops through all the records on the form~

The code below picks up attachments depending if there are attchments on the emails.

To question above... not all emails are the same. The number of attachments will differ depending on the criteria per contact.



////////////////////////////////////////////////////////////
Private Sub cmdSend_click()
Dim stEmail As String
Dim stSubject As String
Dim stBody As String
Dim stName1 As String
Dim stName2 As String
Dim stName3 As String
Dim filename As String

On Error GoTo err:

Dim objOL As Outlook.Application
Set objOL = New Outlook.Application

Dim myApp As New Outlook.Application
Dim myItem As Outlook.MailItem

stEmail = Me.Physical_Email_Address.Value
stName1 = Me.Account_Name.Value & "_IFP_25YrDependents"
stName2 = Me.Account_Name.Value & "_MedicareAgeIns"
stName3 = Me.Account_Name.Value & "_SG_25YrDependents"
stSubject = "BSC Secure:: " & stName & " | **List of Medicare Age Ins in the next 4 months and IFP and Small Group Dependents aging out of parents policies**:::"

'Create the message and include attachment from the folder

Set myItem = myApp.CreateItem(olMailItem)
With myItem
.To = stEmail
If DCount("*", "qry26YrsBroker") > 0 Then
.Attachments.Add "C:\#BrokerNotification\" & stName1 & ".xls"
Else
End If
If DCount("*", "qryMedicareAgeInBroker") > 0 Then
.Attachments.Add "C:\#BrokerNotification\" & stName2 & ".xls"
Else
End If
If DCount("*", "qry26Yrs_SG_Dependents") > 0 Then
.Attachments.Add "C:\#BrokerNotification\" & stName3 & ".xls"
Else
End If
.Subject = stSubject
.ReadReceiptRequested = False
.Body = "Dear " & stName & "," & Chr(13) & _
Chr(13) & _
"blah blah blah blah : " & Chr(13) & _
vbCrLf & _
" (a) blah blah blah blah " & Chr(13) & _
" (b) blah blah blah blah" & Chr(13) & _
" © blah blah blah blah" & Chr(13) & _
" (d) IFP Members who are currently late on paying their premium as of " & Format(Now(), "short date") & vbCrLf & _
vbCrLf & "" & _
vbCrLf & "" & _
vbCrLf & "" & _
"Thank you from the Blue Shield IFP Team~"

.Display
End With
myItem = Nothing
myApp = Nothing

err:
end sub
//////////////////////////////////////////
Go to the top of the page
 
projecttoday
post Jul 19 2018, 11:11 PM
Post#5


UtterAccess VIP
Posts: 10,248
Joined: 10-February 04
From: South Charleston, WV


Re-read the second line of my last post. You do not need to navigate through 5,000 records via a form.

Go to the top of the page
 
JonSmith
post Jul 20 2018, 02:24 AM
Post#6



Posts: 3,956
Joined: 19-October 10



QUOTE
I need the code below to execute for each parent record on the form.
Once the code has execute and the actions have completed, i need it to call the "DoCmd.GoToRecord , , acNext"
and start the process over until it loops through all the records on the form~


Ok, so I gave you an answer, a Do Loop.
You can use that to keep doing the Docmd to move to the next record until you hit the end.
I would, like Robert, not do this approach instead opening a recordset in code and looping through that, both ways would work though.
Give it a shot and see what you get.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2018 - 12:50 PM