Sending email based on meeting condition in Excel

[HelpMeWithExcelPlease]

Hi all,

I am fed up with my apartment complex's inability to notify myself and other residents whenever we receive a package to the building. So I am preemptively making a simple excel file for them to use to notify us when a package is received. After creating a tracking sheet for them I thought it would be even better if I could connect the tracker to their email so that all they have to do is note that a person/apartment received a package and then an email would be generated and sent to the corresponding email address saying they received a package. Unfortunately my VBA skills are not what they use to be, and so I am hoping someone here can help.

Can anyone help me with the VBA code I need to write or if there is a command within excel that I can use to connect with email (Outlook for instance)?

Any help I can get on this issue would be great, thanks in advance!

 

[Logit]

.
Here is a small project you can start with. Pretty much all there ... just need to arrange the Worksheet layout how you want ?

Sub Mail_ActiveSheet()
Dim outapp As Object, cl As Range, bodytekst As Variant, outmail As Object, strlocation As String
'strlocation = "C:\Users\ah0118261\Desktop\Goals 2016-FPM Team.pptx"

 With CreateObject("Outlook.Application").createitem(0)
            For Each cl In ThisWorkbook.Sheets("Choose").Columns(2).SpecialCells(2)
                If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 1).Value) = "yes" Then
                    If strto = "" Then strto = stro & ";"
                    strto = strto & cl.Value & ";"
                End If
            Next cl
            'For Each cl In ThisWorkbook.Sheets("Choose").Columns(2).SpecialCells(2)
            '    If cl.Value Like "?*@?*.?*" And LCase(cl.Offset(0, 2).Value) = "yes" Then
            '        If strcc = "" Then strcc = strcc & ";"
            '        strcc = strcc & cl.Value & ";"
            '    End If
            'Next cl
            .To = strto
            .CC = strcc
            .BCC = ""
            .Subject = "Training Structure"
            .Body = "Please write your content"
            '.Attachments.Add (strlocation)
            .display
        End With
End Sub

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#D8D8D8]

Name​

[/td][td=bgcolor:#D8D8D8]

Email​

[/td][td=bgcolor:#D8D8D8]

Mail?​

[/td][td=bgcolor:#D8D8D8]

​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Name1[/td][td]email1@gmail.com[/td][td]yes[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Name2[/td][td]email2@yahoo.com[/td][td]no[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Name3[/td][td]email3@gmail.com[/td][td]no[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]Name4[/td][td]email4@yahoo.com[/td][td]yes[/td][td][/td][td][/td][td][/td][/tr]
[/table]


Column C is comprised of DropDowns where the user can selected "YES" or "No" to indicate whether an email should be sent to that address. There is also a CommandButton
on the worksheet, attached to the macro, to send the email.

Download link: https://www.amazon.com/clouddrive/share/hsw8YuF6trPxrSjEEYbwHVumuWpXIm1O1cS1xCkLI1P