Email Individual or Selected Individuals Using VBA/Macro Actions

MRBatWork · Apr 11th 2022

Morning, My first time on here for a long time, so apologies if i'm either in the wrong area and so forth. I have checked multiple threads but cant find a answer to this simple query! OK, I have a sheet with a list of people on it which is basically an address book that is located within a project file. Each morning I have to gather information from the web and email only the people i am working with that day....

Now at the moment i click on a send email macro button within the excel project - nice and easy, the macro the runs a simple piece of code we all know where i have pre inserted text and subject, display and send...easy stuff....but i have to remember each person i'm on with, then as i type lots of names come up and eventually I send the email...

What I would like to do is and i cant find a simple answer is ....each morning i Click a button that runs an email macro - (already done) but it gives me the opportunity to select users from a list already created in excel list and send email to only those selected!

Anyone doing this already! must be a way!

Have a great day everyone

**rory** · Apr 11th 2022

I suspect it would help if you posted the code you have currently rather than assuming we all know exactly what code that is.

MRBatWork · Apr 11th 2022

Hi Rory,

Thanks for the reply, of course it would help if i attached the right book with code etc..

Been a long time since i was on here, so thanks for putting this in the right thread. I learnt so much iff here about 2 or 3 years ago and have been loving working with excel since.

This one has me stumped though!

Cheers All

Mr B

**rory** · Apr 11th 2022

That's still an xlsx file so it can't have any code in it?

MRBatWork · Apr 11th 2022

What is wrong with me today....right....

cheers for the patience

Logit · Apr 12th 2022

The following accomplishes the goal you seek. However, it is not presently edited to match your precise layout.

By changing only a few specifics in the code you will have your project complete. Or ... use the attached as is.

Cheers.

Code

Option Explicit

Sub Send_Email()

    Dim c As Range
    Dim strBody As String
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim i As Integer
    On Error Resume Next
    
    
    For Each c In Range("G2:G100")
    strBody = "Greetings : " & c.Offset(0, -6).Value & "<br></br><br></br>" _
                & c.Offset(0, -2).Value & "<br></br><br></br><br></br>" _
                & "Sincerely, " & "<br></br><br></br>" _
                & "Your Signature Here"
                
    
        If c.Value <> "" Then
            Set OutLookApp = CreateObject("Outlook.application")
            Set OutLookMailItem = OutLookApp.CreateItem(0)
            With OutLookMailItem
                    .To = c.Offset(0, -5).Value
                    .CC = c.Offset(0, -4).Value
                    .Subject = c.Offset(0, -3).Value
                    .HTMLBody = strBody   'c.Offset(0, -2).Value
                    .Attachments.Add c.Offset(0, -1).Value
                    .Display
                    '.Send
            End With
        End If
    Next c

End Sub

Sub clrSend()
    Range("G2:G100").Value = ""
End Sub

Display More

Email Individual or Selected Individuals Using VBA/Macro Actions

royUK Apr 11th 2022

Participate now!

Share