Automatic email notification from excel to lotus

voakes

New Member
Joined
Mar 14, 2011
Messages
2
I am wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
If someone would be so kind to help me and to do so in lay terms as I am a bit of a newby at this.
Thanks in advance
Vanessa
 
Hello, thanks for taking the time.

I work for cell B2 I use what you said :

Code:
Sub foo()
    Dim c As Range
        
        For Each c In Range([B2], Cells(Rows.Count, "B").End(xlUp))
            If c.Value <> "OK" Then
                Call charlesrheaultok
                MsgBox "Rappels envoyés " & c.Offset(, -1).Value
            End If
            Next c
End Sub
Sub charlesrheaultok()
     
     ' setting up various objects
    Dim Maildb As Object
    Dim UserName As String
    Dim MailDbName As String
    Dim MailDoc As Object
    Dim attachME As Object
    Dim Session As Object
    Dim EmbedObj1 As Object
    Dim recipient As String
    Dim ccRecipient As String
    Dim bccRecipient As String
    Dim subject As String
    Dim bodytext As String
    Dim Attachment1 As String
    Dim User As String
    
    User = Application.UserName
     
     ' setting up all sending recipients
    recipient = "[EMAIL="charles.rheault@aaa.com"]charles.rheault@aaa.com[/EMAIL]"
     'ccRecipient [EMAIL="=Someoneelse@Somewhereelse.com"]=Someoneelse@Somewhereelse.com[/EMAIL]
     'bccRecipient = ""
    subject = "text"
    bodytext = "text"    
     
     '// Make sure all info has been set before sending!
    If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then
        MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation
        Exit Sub
    End If
     
     ' creating a notes session
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
     
    If Maildb.IsOpen <> True Then
        On Error Resume Next
        Maildb.OPENMAIL
    End If
     
    Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
     
     ' loading the lotus notes e-mail with the inputed data
    With MailDoc
        .SendTo = recipient
         .copyto = ccRecipient
         '.blindcopyto = bccRecipient
        .subject = subject
        .Body = bodytext
    End With
     
     ' saving message (Change to True if you want to save it)
    MailDoc.SaveMessageOnSend = False
     
     Attachment1 = ThisWorkbook.Worksheets("Data").Range("B1").Value
    If Attachment1 <> "" Then
        Set attachME = MailDoc.CreateRichTextItem("Attachment1")
        Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment")
        MailDoc.CreateRichTextItem ("Attachment")
    End If
     
     ' send e-mail
    MailDoc.PostedDate = Now()
     ' if error in attachment or name of recipients
    On Error GoTo errorhandler1
     
    MailDoc.Send 0, recipient
     
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set attachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
     
     'Unload Me
    Exit Sub
     ' setting up the error message
errorhandler1:
    MsgBox "Incorrect name supplied or the attachment has not been attached," & _
    "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _
    "to ensure the application runs correctly and that a vaild connection exists"
     
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set attachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
     ' unloading the userform
     'Unload Me
     
End Sub

The button "notify" calls the macro "foo" and then it works. My question is obviously I need 20 macros for 20 employees to send the email. The foo macro would choose which macro to select depending on if the employee has entered OK next to is cell (let's say employee's name on A2 et "OK" is on B2, going down to B21). Any help is appreciated I understand I am asking a lot.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi All,

This information is really helpful.
I have piece of code here to send an email to lotus notes mail accounts.
But if i need to send an email with out my intervention which is automatically during every month on 9th at 7 AM than how the code changes...

Please guide me!!

Sub semdmail()
Dim Session As Object
Dim Maildb As Object
Dim MailDoc As Object

Range("a2").Activate
While ActiveCell.Value <> ""
EmpNo = ActiveCell.Offset(0, 0).Value
EmpName = ActiveCell.Offset(0, 1).Value
Remark = ActiveCell.Offset(0, 2).Value
Email = ActiveCell.Offset(0, 3).Value

Subject = "hey you are IPMS defaulter"

bodytext = "Dear " & EmpName & " (Employee No.- " & EmpNo & ")" & vbNewLine _
& vbNewLine _
& "You are defaulter you dont have sense-" & vbNewLine _
& vbNewLine _
& Remark & vbNewLine _
& vbNewLine _
& vbNewLine _
& vbNewLine _
& vbNewLine _
& vbNewLine & "Thank & Regards," & vbNewLine _
& vbNewLine & "Rakesh" & vbNewLine _
& "IMCO TCOE " & vbNewLine _
& vbNewLine _
& vbNewLine _
& "This is a fancy Email!!!" & vbNewLine _

Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.getdatabase("", "")

If Maildb.IsOpen <> True Then
Maildb.openmail
End If

Set MailDoc = Maildb.createdocument
MailDoc.form = "memo"

With MailDoc
.sendto = Email
.Subject = Subject
.body = bodytext
End With

MailDoc.savemessageonsend = True
MailDoc.posteddate = Now()
MailDoc.send 0, recipient

Set Session = Nothing
Set Maildb = Nothing
Set MailDoc = Nothing

MsgBox "Email Successfully sent to " & EmpName

ActiveCell.Offset(1, 0).Activate

Wend


End Sub

Regards
Kandanuru
 
Upvote 0

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

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
Back
Top