0

I have a table as following information. I want to send an email in one click to all the users with a corresponding PIN code.

The user should receive the email like this:

Send to (User_Email)

body of the email :

" Hello (user_name) your pin code is (code)"

enter image description here

I have the email server code and it's working fine. I just need the function procedure of finding values for each user with corresponding PIN code and send it to him, then move to the next row and do the same thing until the row is empty. I want to do it in one click.

Public Function Send()
'=========== Dim variables =========
Dim str_body As String
Dim Name As Range
Dim pincode As Range
Dim user_email As Range

'=========== Dim variables =========
'==================================================================
'================< Email server >==================================
Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = "587"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "blah blah@blah.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "blah"
.Update
End With
'==================================================================
' The procedure is to find the value in database with correspond information 
'==================================================================

(( I need the procedure to call the values ))

'===================================================================
'===================================================================
With cdomsg


strBody = strBody & "<span><br/>This Email was sent from Application </span>"

strBody = strBody & "<span>Hello : " & Name & " your pin code is: " & pincode & "</span><br/>"

'====================================================================
.To = user_email
.From = "blah@gmail.com"
.Subject = "Pin code application"
.HTMLBody = strBody
.Send
MsgBox "Login details has been sent to your Email"
End With
Set cdomsg = Nothing
End Function====================================================================```
karel
  • 5,489
  • 46
  • 45
  • 50
Fadi
  • 37
  • 1
  • 6
  • 1
    What about a simple `for each` loop down the list, calling the `Send()` function? – Christofer Weber Oct 20 '20 at 07:43
  • 2
    **1.** Find last row in Col A as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) **2.** Loop through the rows in Column `A` for example `For i = 2 to LastRow` and pick up values like `user_email = Range("B" & i).Value2` and so on. – Siddharth Rout Oct 20 '20 at 07:53

1 Answers1

1

Instead of changing the working function that you have, the easiest approach would probably be to create a sub that loops through the list, calling the function each time to send a mail. There are loads of ways to loop, so just an example:

Sub MainLoop()
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Call Send(Cells(i, 1), Cells(i, 2), Cells(i, 3))
Next
End Sub

This starts at row 2 i = 2 and goes down to the last row. As the data we need is always in the same column, we use i as a row number, and 1 through 3 as column numbers with the Cells() property to fetch our variables.

And then move your variables to be called in the function (or sub) to match what we are calling:

Public Function Send(Name As String, user_email As String, pincode As String)

This will loop through the list and send a mail to each user.
You might want to move the message box to the end of the main loop instead, unless you want to close one for each mail sent.

Christofer Weber
  • 1,464
  • 1
  • 9
  • 18