Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Send All Emails Button - Need Code 1

Status
Not open for further replies.
RobertIngles

RobertIngles

Technical User
Jan 20, 2011
113
CA
Hi all;

I have read through a bunch of posts here and on the web however none seem to fit what I am wanting to do.

I have built an email form that sends emails via outlook (thanks to a sample posting that got me started).

Each email has information pertaining only to the recipient it is addressed to. I have to send out several hundred and am trying to create code for a "Send All Emails" button that can be placed on the form and that, when activated will send all emails to all the records which have been retrieved. I'm not sure if this can be done from the form or if I need to create the button on the switchboard so maybe someone could advise on that. I tried to add a loop statement to the send button but no luck - lack of coding experience vs hit and miss is stalling me.

The currect code associated with my send button is:

Private Sub Command20_Click()

Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><H2>"
.To = Me.Email_Address
.Subject = Me.Mess_Subject
.HTMLBody = Me.mess_text
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'.DeleteAfterSubmit = True
.Send
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out

Error_out:
End Sub

Any help would be appreciated.
 
Sort by date Sort by votes

Looks like you are sending this e-mail to just one person:
Code: 
 .To = Me.Email_Address
 .Subject = Me.Mess_Subject
 .HTMLBody = Me.mess_text
from some fields on your Form (Email_Address, Mess_Subject, mess_text, etc.)

So, where do you have any list of e-mail addresses, Subjects (if there is different Subject per recipient), body of the e-mail?)

If you can read this information from some lists (list box, recordset, etc) - instead of just one particular person's - then you are almost there.... :)

Have fun.

---- Andy
 
Upvote 0 Downvote
Hey Andy - thanks...

All the information is in a single table created from extracting email addresses in a User table [User_Info} and extracting device status informaiton from my [Device_Info table] via a query based on the status of the device (primary link is [User_ID] - [User_ID] can be associated with mulitple [Device_Name].

The results (User_First_Name, User_Email_Address, Device_Name) are used to create a new table [Email_Info] and then I populate the subject line, message and attachment fields using an update query to the same table. Each batch of messages I send can be slightly different text depending on the status of the Device).

Table is created this way:

Device_Name (from [Device_Info]Populated by Make Table Query
User_ID (from [User_Info) Populated by Make Table Query
User_Email_Address (from [User_Info] Populated by Make Table Query

Subject (Populated via an update query and data inserted from [Device_Info]...Text & [Device_Name]
Message (Populated with update query and data inserted from [Device_Info]...Text& [User_First_Name]&[Device_Name])
Attachment (Poplulated with update query)

So when I open the FRM_Email which is based on the table [Email_Info]I am looking at all the results from the [Email_Info] table - maybe 200 or 300 records. Rignt now I am clicking send 200 or 300 times to send via outlook.

So yes, right now it is set up to send a single email. How can I send all the emails at once. The form works beautifully and there are no pop-ups from Outlook to deal with. I click send from my form and it is gone. I also have all messages I sent in my Outlook sent folder so as far as that goes it works well.

Thanks for your help on this!


 
Upvote 0 Downvote

So if you click on Command20 command button (I would change the name if it) you send one message, one e-mail to one person.

How does it point to the next record so you can send an e-mail to the next person? What's your logic? Show the code that moves you to the next record.

Have fun.

---- Andy
 
Upvote 0 Downvote
acCmdRecordsGoToNext? Then I would have to put in a Loop statement?
 
Upvote 0 Downvote

Then how do you know when you are done? How do you know when you get to the last record? Do you get an error or something?

Consider this code - a little modification of your original code, but I did not test it (red piece just added, but I usually read the info from the recordset rst):
Code: 
Option Explicit
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem

Private Sub SendAllEMails_Click()
Dim i As Integer

Set appOutLook = CreateObject("Outlook.Application")

For i = 1 to rst.RecordCount
    Call SendAnE_Mail(Me.Email_Address, _
        Me.Mess_Subject, Me.mess_text, _
        Me.Mail_Attachment_Path)
[red]
    acCmdRecordsGoToNext[/red]   [green]'???[/green]

    [green]'rst.MoveNext[/green]
Next i

Set appOutLook = Nothing

MsgBox "I am DONE sending e-mails."

End Sub

Private Sub SendAnE_Mail(strEMail As String, _
    strSubject As String, strBody As String, _
    strAttPath As String)

Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
    .BodyFormat = olFormatHTML
    .HTMLBody = "<HTML><H2>"
    .To = strEMail         
    .Subject = strSubject  
    .HTMLBody = strBody     
    If Left(strAttPath, 1) <> "<" Then
        .Attachments.Add (strAttPath)
    End If
    [green]'.DeleteAfterSubmit = True[/green]
    .Send
End With

Set MailOutLook = Nothing

[green]'MsgBox MailOutLook.Body[/green]
Exit Sub
email_error:
  MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
  Resume Error_out
            
Error_out:

End Sub

[tt]
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
[/tt]
Those 2 lines may be in the wrong place, but I hope they are right

Have fun.

---- Andy
 
Upvote 0 Downvote
Thanks, I have been trying and I think I am close but Access returns an error saying it doesn't recognize the .Send - I will try your code:

Do
.Send
Rst.MoveNext
Loop

Do Until Rst.EOF
Loop

End With
'MsgBox MailOutLook.Body
Exit Sub
 
Upvote 0 Downvote
Hi Andy;

trying your code but keep getting the error "Object Required" When I debug it highlights:

For i = 1 To rst.RecordCount
 
Upvote 0 Downvote

It is because you do NOT have a recordser named rst

Instead of this code:
[tt]
For i = 1 to rst.RecordCount
Call SendAnE_Mail(Me.Email_Address, _
Me.Mess_Subject, Me.mess_text, _
Me.Mail_Attachment_Path)
acCmdRecordsGoToNext [green]'???
'rst.MoveNext[/green]
Next i[/tt]

you need something like (pseudo code here):
[tt]
Move down the records you need to proccess
Call SendAnE_Mail(Me.Email_Address, _
Me.Mess_Subject, Me.mess_text, _
Me.Mail_Attachment_Path)

acCmdRecordsGoToNext
[/tt]
It comes down to the questions:
1. How do you know how many records you have?
Which translates to: "How many e-mails are you going to send?"
2. How can you point to the very first record?
3. How do you know when you have the last record?

Have fun.

---- Andy
 
Upvote 0 Downvote
Thanks for your help Andy - still doesn't work so I'll have to send them individually until I can find someone here to work with me to learn VBA. I'm top notch when it comes to creating processes through Access's built in methodologies however VBA is still pretty much Russian to me.

Thanks again!
 
Upvote 0 Downvote

Believe it or now – I am not an Access guy. I am a “VB 6 – Oracle – ADODB – deal with everything in the code – hates binding Forms/controls to magic back end queries” kind of guy.

So if you have some kind of query that gives you all information needed to send those e-mails (I would guess your source to populate your fields - Email_Address, Mess_Subject, mess_text, Mail_Attachment_Path - on your FRM_Email). If you have all of it in one recordset (rst):
[tt]
EmailAdd MSubject mtext MAttPath
john@msn.com Hi Hello C:\Junk.pdf
ABC@Yahoo.com Ola Hi There C:\File.jpg
XYZ@Google.com Giasas Ela C:\Movie.avi[/tt]

And you get all of these by:[tt]
Select EmailAdd, MSubject, mtext, MAttPath
From SomeTable
Where ...[/tt]

Then I usually do:
Code: 
Private Sub SendAllEMails_Click()
Dim rst As RecordSet
Dim i As Integer
dim s As String

s = "Select EmailAdd, MSubject, mtext, MAttPath " _
   & " From SomeTable " _
   & " Where ..."

rst.Open s, SomeConnection

Set appOutLook = CreateObject("Outlook.Application")

For i = 1 to rst.RecordCount
    lblMsg.Caption = "Sending " & i & " of " & rst.recordCount
    lblMsg.Refresh

    Call SendAnE_Mail(rst.EmailAdd, _
        rst.MSubject, rst.mtext, _
        rst.MAttPath)

    rst.MoveNext
Next i

rst.Close

Set appOutLook = Nothing

Beep
lblMsg.Caption = "I am DONE sending e-mails."

End Sub

Have fun.

---- Andy
 
Upvote 0 Downvote

OK. I did ask several questions, but no answers. That's fine - you may not know the answers to them.

But... Assuming you do know how many records/e-mails you want to send (200, 300, 268), you can have all the clicks done by the code.

Place a command button next to your Command20 and name it [tt]cmdSendAll[/tt], place this code in the click event for this button:
Code: 
Private Sub cmdSendAll_Click()
Dim intHowMany As Integer
Dim i As Integer

intHowMany = Val(InputBox("How many E-Mails do you want to send?", _
    "How Many?", 0))

If intHowMany > 0 Then
    For i = 1 To intHowMany
        cmdSendAll.Caption = i & " of " & intHowMany[blue]
        Call Command20_Click[/blue]
    Next i
End If

Beep
cmdSendAll.Caption = "DONE."

End Sub
When you click on it, the InputBox will appear and ask you how many e-mails do you want to send. If you type 268, your Command20 button will be 'clicked' 268 times.

I would call it "brute force VBA programming 101" - not very efficient or nice, but it gets the job done. :)

Have fun.

---- Andy
 
Upvote 0 Downvote
Thanks Andy - sorry, I was in meetings this afternoon. I'll be burining the midnight oil with this to try it out - fingers crossed and stay tuned, I'll udpate you in the morning!
 
Upvote 0 Downvote
Small problem Andy.

If I put say 12 in the number of times to click send, it sends the email 12 times to the record it is on. Do I need a GoTo or NextRecord command in there somewhere?
 
Upvote 0 Downvote
Fixed! Added DoCmd.GoToRecord , , acNext after the .Send command.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ameedoo3000
  • Locked
  • Question
format partition by vba code
Replies
6
Views
686
mattKnight
mattKnight
TheresAlwaysAWay
  • Locked
  • Question
How to send/receive text/SMS message through Access
Replies
1
Views
213
jedraw
jedraw
atadbitslow
  • Locked
  • Question
need help with syntax for a date
Replies
3
Views
235
dhookom
dhookom
marnb
  • Locked
  • Question
I need outlook to send out an email
Replies
2
Views
169
dhookom
dhookom
qqp
  • Locked
  • Question
Need my msgbox that warns user of missing criteria to stop the rest of the code from running
Replies
2
Views
231
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top