Automatic sending of email

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Morning all.

I know that the following is possible and is likely done in VBA or a macro (trust me when I say that's the limit of my knowledge on either of them but for the rest of this post I'll refer to it as code) but I'm looking for an email to be sent via Microsoft Outlook when a cell has a certain value entered into it.

But there is a little more to that as well as follows - hopefully the image will help with what I need some serious high level help with

On Sheet 1 name/initials will be entered in column A.
When Y is entered in column B, the code will need to go to sheet 2, scan down the list of names in column A so that it can find the correct email in column B.
The code then needs to open up a Microsoft new email with the correct email address AND in the subject line take the data from sheet 1 cells and place it in the subject of the email

Image.jpg


In many ways I know that this may all sound very simple but in reality I know it will be a big thing to get this to work and anyone brave enough to try and get this to work will have to remember that when it comes to this level of code I have absolutely no idea whatsoever of what id be doing.

Best wishes

Damian
 

Attachments

  • Image.jpg
    Image.jpg
    94.3 KB · Views: 4

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a "Y" in column B and press the RETURN key or TAB key. The email will be automatically created.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, rName As Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    If Target = "Y" Then
        Set rName = Sheets("Sheet2").Range("A:A").Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rName Is Nothing Then
            With OutMail
                .To = rName.Offset(, 1)
                .Subject = Range("D" & Target.Row).Value & ", " & Range("E" & Target.Row).Value & ", " & Range("F" & Target.Row).Value
                .HTMLBody = ""
                .Display
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub
 
Solution

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Good Morning Mumps.

I have to admit I didn't expect to get a reply so quickly with an actual answer to my query.

Many many thanks for taking the time to help with this - I shall now implement it in time to return to work in the New Year.

Whatever your Christmas brings you this year I hope it at least includes a smile or two and memories to keep.

Stay safe.

D
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top