Email notification with row values

Thomas Kuriakose · Apr 2, 2015

Dear All,

Kindly find attached an excel file where on selection of validated data in column P an email should be sent to specific users -

Column P is the Trigger Column =

When the selected value in P is -

(a) Repair Prospects : The complete row field data from Col A:S along with the headers should be emailed in a new sheet to email recipients as per the Locations in column R with copies to the supervisor of the particular location.
(b) MOD Prospects: The complete row field data from Col A:S along with the headers should be emailed in a new sheet to email recipients as per the Locations in column R with copies to the supervisor of the particular location.

Kindly help with a vba code to get this data.

thank you so much

with regards,
thomas

Luke M · Apr 2, 2015

Hi Thomas,

Where would the actual email addresses be stored? Col R currently says things like North/West/South.

When user changes value in col P, is there in difference really in what happens for "MOD Prospects" vs "Repair Prospects"?

When email is sent, do we send all the records (rows 5310), or just the row that user just changed (along with the headers)?

Once you know these answer, I'm probably going to start here:
http://www.rondebruin.nl/win/s1/outlook/mail.htm

and see if one of the already built macros can be modified to suit.

Thomas Kuriakose · Apr 2, 2015

Dear Luke,

Thank you so much for your kind help.

1. The email addresses should be in the code (if I understood your query correctly). Kindly put in my email id for the test: thomask_70@yahoo.com
2. The column R has Locations based on Zones (North/West/East/South). Currently there are members responsible for each zone. If this needs more clarification we can omit this reference from the code for now. Once it starts working, I will check with the users on the location wise matrix for emails.
3. When the value on P changes , for example if the selection is MOD Prospects, the email will go only to the concerned who deal with MOD with cc to the supervisors and same for Repair prospects.
4. When email is sent only the current record (complete row data with header data) based on the selection of column P (MOD/Repair). We can start with one record for the selected P.

I hope I was able to answer your queries correctly.

Thank you so much once again.

with regards,
thomas

Thomas Kuriakose · Apr 2, 2015

Dear Luke,

sorry missed out one point, the selected data should be saved as new file and sent with file name : with MOD/Repair Prospects and the current date when it is being sent.

Thanks,

with regards,
thomas

Luke M · Apr 2, 2015

Well, that answers most of it. Let's start with this.

An event macro on the sheet figures out if we need to run the macro. If it does, it calls the Mail_Range macro, telling it what range to send, and who to email. I only knew your email for now, but you can adjust as needed.

Read through the Mail_Range macro. I got it from Ron's site here:
http://www.rondebruin.nl/win/s1/outlook/amail4.htm

and he does a good job with comments. Macro currently only displays the email, it doesn't send it (so as not to accidentally email something while we are testing).

From this point, I believe you need to figure out how you want to determine who the email gets sent/cc to.

Thomas Kuriakose · Apr 3, 2015

Dear Luke,

Thank you so much for the solution provided.

I missed to inform about the mail application. The application used is Lotus Notes. The macro worked but it gave a run time error due to outlook.

I tried to change the application but, I am not able to use the correct application name. I am still trying.

Thank you so much for your kind help.

with regards,
thomas

Thomas Kuriakose · Apr 3, 2015

Dear Luke,

Can we have the code without opening mail object. The default mail application will be used on clicking send button.

I don't know whether my question is correct.

What the user is saying is the Back office personnel needs to only key in the data and select the value in column P and on selection the email should go to the addressed recipients. Can we only use the email addresses for sending without opening email application.

I tried to not use the create object but it is not working -

'Set OutApp = CreateObject("Outlook.Application")
'Set OutMail = OutApp.CreateItem(0)

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
'With OutMail
.to = "thomask_70@yahoo.com"
.CC = "" 'This is where you list who to email to
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add Dest.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
'.Send

Kindly assist

Thanks,

with regards,
thomas

Thomas Kuriakose · Apr 3, 2015

I tried to use some codes from mrexcel.com for attaching excel file to lotus notes, but failed, below in Italics are the changes made to the original code. it is giving run time error 438: Object does not support this property or method.

Code:

Option Explicit

Sub Mail_Range(RangetoMail As Range, emailAdd As String)
'Working in Excel 2000-2013
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Session As Object
    Dim MailDoc As Object

    Set Source = Nothing
    On Error Resume Next
    Set Source = RangetoMail.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2013
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If

    Set Session = CreateObject("Notes.NotesSession")
    'UserName = Session.UserName
    'MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    'Set Maildb = Session.CreateItem(0)
    Set MailDoc = Session.CREATEDOCUMENT
    'If Maildb.IsOpen = True Then
    'Else
    'Maildb.OPENMAIL
    'End If

' Create New Mail and Address Title Handlers

'Set MailDoc = Maildb.CREATEDOCUMENT
    'Set OutMail = Session.CreateItem(0)

    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With MailDoc
            .to = "thomask_70@yahoo.com"
            .CC = ""  'This is where you list who to email to
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add Dest.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            '.Send
            .display 'While debugging, we won't actually send out the emails
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

    'Uncomment this line to delete temp file
    Kill TempFilePath & TempFileName & FileExtStr

    Set Session = Nothing
    Set MailDoc = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Can we send the attachment without opening the email application.

thank you so much.

with regards,
thomas

Luke M · Apr 6, 2015

No, in short, you can't send an email w/o opening an email application. XL doesn't have email capabilities on it's own.

As I don't have Lotus Notes on my machine, I can't test your code, or know how to advise to get it working.

Which line was getting highlighted when the code failed?

Thomas Kuriakose · Apr 6, 2015

Dear Luke,

The error comes in Call mail range

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const checkVal  As String = "Prospects"
Dim sendRange As Range

'What range to watch?
If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'In case user mass deletes information

If Right(Target, Len(checkVal)) = checkVal Then
    Set sendRange = Union(Range("A3:S4"), Range(Cells(Target.Row, "A"), Cells(Target.Row, "S")))
    Call Mail_Range(sendRange, "thomask_70@yahoo.com")
End If
End Sub

Thank you so much.

with regards,
thomas

Thomas Kuriakose · Apr 6, 2015

Dear Luke,

I found another code in the net and used this on a test sheet, this is working in lotus notes, but the sheet does not have the inputs of my first file.

Kindly see if we can incorporate these codes in our original file.

Thank you so much for your kind support.

Cannot upload the file. This is showing error.

Code:

Private Sub CommandButton1_Click()
Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "C:\Users\kuriakth\Desktop"

Const stSubject As String = "Weekly report"

Const vaMsg As Variant = "The weekly report as per agreement." & vbCrLf & _
                                          "Kind regards," & vbCrLf & _
                                          "Dennis"

Const vaCopyTo As Variant = "thomas.kuriakose@ae.schindler.com"

  Dim stFileName As String
  Dim vaRecipients As Variant
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
  Dim stAttachment As String
  'Copy the active sheet to a new temporarily workbook.
  With ActiveSheet
    .Copy
    stFileName = .Range("A1").Value
  End With
  stAttachment = stPath & "\" & stFileName & ".xlsx"
  'Save and close the temporarily workbook.
  With ActiveWorkbook
    .SaveAs stAttachment
    .Close
  End With
  'Create the list of recipients.
  vaRecipients = VBA.Array("thomask_70@yahoo.com", "thomask_70@yahoo.com")
  'Instantiate the Lotus Notes COM's Objects.
  Set noSession = CreateObject("Notes.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")
  'If Lotus Notes is not open then open the mail-part of it.
  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
  'Create the e-mail and the attachment.
  Set noDocument = noDatabase.CreateDocument
  Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
  Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
  'Add values to the created e-mail main properties.
  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipients
    .CopyTo = vaCopyTo
    .Subject = stSubject
    .Body = vaMsg
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
  'Delete the temporarily workbook.
  Kill stAttachment
  'Release objects from memory.
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
  MsgBox "The e-mail has successfully been created and distributed", vbInformation
End Sub

I will clip the code.

with regards,
thomas

Thomas Kuriakose · Apr 6, 2015

now managed to clip the file

Luke M · Apr 7, 2015

Hi Thomas. I think I managed to combine the two items. Let me know if this works or not.

Thomas Kuriakose · Apr 7, 2015

Dear Luke,

This is excellent, this is working perfectly. thank you so much for your kind help.

One more request, we need two more changes -

1. Can we have the file name based on the selection, ie, MOD or Repairs
2. Can the mail be sent to specific users based on the selection, MOD or Repairs.

Thanks a ton for this support.

with regards,
thomas

Luke M · Apr 7, 2015

Yes, but you'll need to either have that information in the file somewhere, or tell us exactly what you want. My mind reading skills are still under-developed.

Thomas Kuriakose · Apr 8, 2015

Dear Luke,

Apologies for not providing the correct information.

Kindly find attached file with the requirement.

Thank you so much for your kind help.

with regards,
thomas

Luke M · Apr 8, 2015

That's a start. Here's the new file. Look in the SendRange macro, and you can see where I put a spot for you to list the emails, and then the If check at beginning which figures out which set of addresses and file name to use.

Thomas Kuriakose · Apr 9, 2015

Dear Luke,

Thank you so so much for this solution provided.

Thanks once again.

with regards,
thomas

Email notification with row values

Active Member

Attachments

Excel Ninja

Active Member

Active Member

Excel Ninja

Attachments

Active Member

Active Member

Active Member

Excel Ninja

Active Member

Active Member

Active Member

Attachments

Excel Ninja

Attachments

Active Member

Excel Ninja

Active Member

Attachments

Excel Ninja

Attachments

Active Member