Send email when a new row is added ...

[adambc]

During my career, I considered my self a reasonably advanced Excel user (I'm pretty proficient in using eg Pivot Tables, complex formulas, etc), but I never used Power Query and certainly not VBA - I'm now retired and recently starting volunteering for a local charity, helping them to make better use of Excel to manipulate their data into the reports they need internally and for external reporting. I've already/succesfully mastered Power Query to reshape some external data and merge it with internal data, but I now find myself in the world of VBA - but I've never done any "coding" before, so I'm on a very steep learning curve!!!

The use case/requirements are ...

We have an "Incident Log" held in Excel, that consists of an individual record (row) for each incident and around 20 columns (some based on drop down lists, others free format). I've built a macro in VBA which sits behind a custom button on the ribbon, that emails a person named in one of the columns a subset of the column values FOR THE LAST ROW in the table. This works fine, but it relies on the operator remembering to click the button after they have finished adding a new record, before they add another new record.

What I would really like to do is have the macro determine how many new records have been added to the log when the log is saved and then create/send a separate email for each new record (each incident is unique and the person named/to whom the email has to go could be different for every incident) - but I am at the absolute limit of my newly learned VBA skills and am struggling with i) is this even possible and ii) if it is, how/what does the VBA code look like?

Can anyone help me please - I cannot tell you the difference it will make to the charity if I can get this working (they have several almost identical requirements, all driven by their need to be able to show that they have reacted to incidents in a timely manner)?

Many thanks ...

PS; I'm posting this in my own time and don't currently have access to the VBA macro I've already written, but it's loosely based on a number of posts I've found on the internet - I just can't find anything that fits what I'm trying to do now!!!

 

[mrshl9898]

This should get you started:

I'd suggest having column 21 being either blank or "Email Sent" to mark off what has been done.

It will start at row 2, looking for those and sending ones unmarked. It does rely on column 1 containing data, and in this case the email address. You can see the Email_To where to change this.

Public rownum As Long

Sub ChecktoSend()

rownum = 2

Do Until Cells(rownum, 21).Value = ""
    If Cells(rownum, 21).Value = "" Then
    SendEmail
    Cells(rownum, 21).Value = "Email Sent"
    End If
rownum = rownum + 1
Loop

End Sub



Sub SendEmail()

Dim EmailSubject As String, EmailSignature As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
DisplayEmail As Boolean
Dim OutlookApp As Object, OutlookMail As Object

    EmailSubject = "Subject Here"   'Change this to change the subject of the email.
    DisplayEmail = False 'Change this if you don't want to display the email before sending.  Note, you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Cells(rownum, 1)  'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
    Email_CC = ""
    Email_BCC = ""

    'Create an Outlook object and new mail message
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    'Display email and specify To, Subject, etc
    With OutlookMail

        .Display
        .To = Email_To
        .CC = Email_CC
        .BCC = Email_BCC
        .Subject = EmailSubject

' set to true to view email before sending

        If DisplayEmail = False Then

            .Send

        End If

    End With

End Sub

A next step would be to add this under the ThisWorkbook module, to trigger the macro when save is pressed.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ChecktoSend
End Sub

 

[adambc]

Many thanks - quick question ... where do I put my Email_Body code?

 

[adambc]

Please ignore my quick question - I knew the answer but wasn't thinking!

Have got this working perfectly!

HUGE THANKS

 

[mrshl9898]

Glad to hear, thanks for the feedback.

 

[adambc]

I've got everything running perfectly apart from adding the default Outlook signature when I add .Body (which is a string of "text " & " " & (rownum, ?) & vbNewLine etc, etc) to the email ie ...

With OutlookMail

.Display
.To = Email_To
.CC = Email_CC
.BCC = Email_BCC
.Subject = EmailSubject
.Body = EmailBody (defined earlier in the routine)

End With

(I can't use .HTMLBody because that removes all new (vbNewLine) lines ie I get a continuous string of text/values - and that still doesn't add the signature anyway!)

If I comment out .Body, the resulting email (obviously!) doesn't have any body - but does have the standard Outlook email HTML block ...

If I include .Body, the resulting email does have a body - but doesn't have any signature at all ...

I've looked at several solutions online, but none seem to do what I need ie .Body with all it's new lines etc and the default Outlook (HTML) signature block

Can you help please?

Many thanks ...

 

[adambc]

UPDATE ...

By amending .Body = EmailBody to .HTMLBody = EmailBody & .HTMLBody, I now get both the body and the default Outlook signature, but the EmailBody part is still as a continuos string ie without the vbNewLine(s) ...

And if I use .Body = EmailBody & .HTMLBody, again I get both, but the signature is no longer HTML ...

And ...

.Body = EmailBody
.HTMLBody = .HTMLBody

… doesn't work either!!!

Is there a way to mix non HTML (.Body) and HTML (.HTMBody)?

 

[adambc]

OK, fixed it!!!

.Body = EmailBody & .HTMLBody - but replacing vbNewLine with "<br>" in EmailBody = "??????"

 

[mrshl9898]

Missed this while on holidays, have wondered the same myself, so thanks for posting a solution!