Emailing multiple emails with independent values

[iamcodym]

Hi! New poster to the forum, though I've long perused for answers. I hope to contribute help myself going forward.

In the mean time, I've searched for an answer to this and have yet to find an answer (if something has already been posted, forgive me).

I'm a team lead at my place of employment, and we track login information in spreadsheets. The way our tracking spreadsheet is currently setup allows me to select one of 30 people in my group, and click a button which launches my macro sending an email to that particular person with only their login information. As you can imagine, this can be a bit tedious with a group of 30.

Is anyone familiar with a way to write the macro to send the unique login form to all 30 agents individual emails at the same time? Essentially creating 30 unique emails with unique cell values sent to 30 unique email addresses with one click of a button?

Please let me know if you need additional information, any help is greatly appreciated!

Additional info - all team leads have access to Excel 2010, so no need for compatibility with prior versions. Same with Outlook.

 

[sergioMabres]

I thinks if the macro reads a range with let us say information about each indivitual mail it can send n different mails

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Integer
    Set OutApp = CreateObject("Outlook.Application")
    ' Do 30 times reads a range
    '   B has email
    '   C has name
    '   C has PWD
    For i = 1 To 30
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
       ' Change the mail address and subject in the macro before you run it.
        With OutMail
            .to = Worksheets("Sheet1").Range("B" & i + 1).Value
            .CC = ""
            .BCC = ""
            .Subject = "Login information for " & Worksheets("Sheet1").Range("C" & i + 1).Value
            .Body = "Your login info has changed, you new password is: " & Worksheets("Sheet1").Range("D" & i + 1).Value
            .Send
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    Next i
    Set OutApp = Nothing
End Sub

I hope the macro helps

 

[iamcodym]

Thanks a lot! I will tinker with this, in the mean time, perhaps you (or someone) could recommend how we could utilize this in the following situation.

On Sheet 1 we have the roster of team members in Column A, and Columns B-Z would have all their collective usernames and passwords.

On Sheet 2, I would have 30 forms filled out with each team members logins using a lookup/array. This way all the logins are formatted into an easily copy/pasted form, and look "pretty" when emailed.

How could we use this macro to send those forms from Sheet 2, rather than just a single cell with their new password. Know what I mean? Hopefully this makes sense.

Again, appreciate the help!

 

[sergioMabres]

You can adapt the macro, or use parts of the macro to write the macro you need

.to = Worksheets("Sheet1").Range("B" & i + 1).Value                                                          ' Change range to where the info really is
.Subject = "Login information for " & Worksheets("Sheet1").Range("C" & i + 1).Value                          ' Change range to where the info really is 
.Body = "Your login info has changed, you new password is: " & Worksheets("Sheet1").Range("D" & i + 1).Value ' Change range to where the info really is

You have two ways to do this, you can write the macro yourself or you can ask someone to write the macro for you.
What is left is quite simple just tell the macro where the info is and move the info to the parameters on the mail and send the 30 mails
To write the macro the worksheet is needed, even if filled with bogus information the real address of the cells are needed
I can write the macro for you

 

[iamcodym]

I will give it a shot and let you know. Thanks a lot friend!