Creating an email from data from a sheet

[jugger0s]

Excel version - 2013

Hello, I've been trying to figure this out for a while with no joy.

data example

Venue	Provider	Date	Time	Enrolments	Register	Lesson Plan
E2	Alpha	14/11/2014	15:00	44	Received	Received
E4	Alpha	16/11/2014	16:00	15	Received	Received
E5	Bravo	04/09/2014	17:00		Received	Received
E3	Bravo	06/03/2014	18:00	43		Received
E3	Charlie	04/02/2015	19:00	2	Received	Received

<colgroup><col span="2"><col><col span="3"><col></colgroup><tbody>
</tbody>

Ok, What I'm trying to do is for each row i would like to make a button or selection somewhere that will select the row and create an email with that rows data. For example A button or macro in the venue column that will open an email, and add the enrolments, register and lesson plan information from that row. I've done something like that in Access for a database form but it doesn't translate.

This is what I would like to see in the email,

Enrolments Register Lesson Plan
44 Received Received

Then once this is working i'll then tackle the tricky task of defining the blanks to show what is required from who i send the email to... but thats part two...

If anyone had any idea it will be greatly appreciated.

Thanks for your time,

Gareth.

 

[SRAnalyst]

Create a macros using Relative References and saved into your personal macros, select a row with data, copy, create new tab, rename tab, paste contents into new tab, FILE>Save and send, pick the format and save.

Now every time you need to send a diferent row, you just have to select the row and run the macros.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I use something like this:

' Tick: Tools-->References: Microsoft Outlook 15.0 Object Library
Sub CreateEmail(ThisRow As Long)

    '====================================================================================
    ' Get Data from Excel
    '====================================================================================
    Dim Enrolments As String
    Dim Register As String
    Dim LessonPlan As String
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Enrolments = ws.Range("E" & ThisRow).Value
    Register = ws.Range("F" & ThisRow).Value
    LessonPlan = ws.Range("G" & ThisRow).Value
    
    '====================================================================================
    ' Create Email
    '====================================================================================
    Dim oOlApp As Outlook.Application
    On Error Resume Next
    Set oOlApp = GetObject(, "Outlook.Application") ' Outlook needs to be already open
    If Err.Number > 0 Then ' If not open then open Outlook
        On Error GoTo 0
        Err.Number = 0
        Set oOlApp = CreateObject("Outlook.Application")
    End If

    Dim oOLMail As MailItem
    Set oOLMail = oOlApp.CreateItem(0)
    Dim strBody As String
    Dim tmpBody As String
    
    strBody = "Hi, <br>" & _
              "<br> Enrolments: " & Enrolments & "<br>" & _
              "<br> Register: " & Register & "<br>" & _
              "<br> LessonPlan: " & LessonPlan & "<br>" & _
              "<br>Regards,"
         
    With oOLMail
        .Display (False)
        tmpBody = .HTMLBody ' Store the sig to re-add it later
        .To = "Someone.Else@xyz.com"
        .CC = "Support Inbox"
        .BCC = ""
        .Subject = "Enrolments request: " & Enrolments
        .BodyFormat = olFormatHTML
        .HTMLBody = "<span style=""font-family: HPFutura Book; font-size: 13pt;"">" & strBody & "<br>" & tmpBody & "</span>"
        .Display (True)
    End With
    
    ' Leave Outlook open so that the email can be sent
    
End Sub

That is a macro that resides in a Module and needs to be given the row number that you want to process.
Note that the Microsoft Outlook 15.0 Object Library is required.

Instead of using buttons I prefer to right-click a cell. I picked column A for this example. The calling program can then go behind the worksheet:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Application.EnableEvents = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    If Target.Count = 1 And Target.Value <> "" Then ' Only one, non-blank cell selected?
        If Not Intersect(Target, ws.Columns(1)) Is Nothing Then  ' Selection in column 1?
            Call CreateEmail(Target.Row)
            Cancel = True  ' Switch off the right-click menu
        End If
    End If
    Application.EnableEvents = True
End Sub

With both those macros in place you can right-click somewhere in column A and an email will be created (assuming you use Outlook like I do!)

Obviously, you will need to modify strBody, Subject addressees etc.
Note the trick to add the sig.

 

[RickXL]

That first block of code should have looked like this:

' Tick: Tools-->References: Microsoft Outlook 15.0 Object Library
Sub CreateEmail(ThisRow As Long)

    '====================================================================================
    ' Get Data from Excel
    '====================================================================================
    Dim Enrolments As String
    Dim Register As String
    Dim LessonPlan As String
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Enrolments = ws.Range("E" & ThisRow).Value
    Register = ws.Range("F" & ThisRow).Value
    LessonPlan = ws.Range("G" & ThisRow).Value
    
    '====================================================================================
    ' Create Email
    '====================================================================================
    Dim oOlApp As Outlook.Application
    On Error Resume Next
    Set oOlApp = GetObject(, "Outlook.Application") ' Outlook needs to be already open
    If Err.Number > 0 Then ' If not open then open Outlook
        On Error GoTo 0
        Err.Number = 0
        Set oOlApp = CreateObject("Outlook.Application")
    End If

    Dim oOLMail As MailItem
    Set oOLMail = oOlApp.CreateItem(0)
    Dim strBody As String
    Dim tmpBody As String
    <pre>
    strBody = "Hi, <br>" & _
              "<br> Enrolments: " & Enrolments & "<br>" & _
              "<br> Register: " & Register & "<br>" & _
              "<br> LessonPlan: " & LessonPlan & "<br>" & _
              "<br>Regards,"
    </pre>
    With oOLMail
        .Display (False)
        tmpBody = .HTMLBody ' Store the sig to re-add it later
        .To = "Someone.Else@xyz.com"
        .CC = "Support Inbox"
        .BCC = ""
        .Subject = "Enrolments request: " & Enrolments
        .BodyFormat = olFormatHTML
<pre>
        .HTMLBody = "<span style=""font-family: HPFutura Book; font-size: 13pt;"">" & strBody & "<br>" & tmpBody & "</span>"
</pre>
        .Display (True)
    End With
    
    ' Leave Outlook open so that the email can be sent
    
End Sub

My apologies.

I will get the hang of this inter-web stuff eventually!