vba - send email to lotus notes via excel

[george hart]

Hi

I need code that sends an e-mail to a specific person (lotus notes) if range "A1" = Overdue.

The email address of the person needing the email will be in cell "B1" and so on B2, B3 Etc if the cell next to it in column A reads overdure.

The code below captures my range but not how to send the messge to the recipient...Any ideas most welcome.

Dim x As Integer
For x = 3 To Cells(Rows.Count, "A").End(xlUp).Row

If Range("A" & x) = "Overdue" Then _
'I need the code to go here to send a messege that reads, "Please esnure you update your files etc...

Next
End Sub

Many thanks in advance...

 

[george hart]

Try this:

Public Sub Notes_Email_Workbook()

Dim recipients As Variant
Dim emailBodyText As String
Dim NewFileName As Variant

'This needs to reflect the correct file location and name
NewFileName = "C:\Documents and Settings\HartG\My Documents\Projects\Sickness\New Macros" & "\" & "Absent-Restriction Procedural documentV3" & ".pdf"

recipients = "Add recipient"
emailBodyText = "Please see attached bla bla bla"

Create_and_Send_Notes_Email "Please see attached bla bla " & Now, recipients, emailBodyText, Array(ThisWorkbook.FullName, NewFileName)

End Sub



Private Sub Create_and_Send_Notes_Email(Subject As String, recipientsArray As Variant, BodyText As String, Attachments As Variant)

Const EMBED_ATTACHMENT As Long = 1454

'Declare objects for Lotus Notes automation

Dim NSession As Object 'NotesSession
Dim NMailDb As Object 'NotesDatabase
Dim NDoc As Object 'NOTESDOCUMENT - the mail document itself
Dim NRichTextItem As Object 'The attachment rich text file object
Dim NEmbeddedObj As Object 'The embedded object (Attachment)
Dim AttachmentsArray As Variant
Dim i As Integer

'Start a Notes session

Set NSession = CreateObject("Notes.NotesSession") 'Lotus Notes Automation Classes (OLE)
Set NMailDb = NSession.GETDATABASE("", "") 'uses the default .nsf database

If Not NMailDb.IsOpen Then
NMailDb.OPENMAIL
End If

'Create a new mail document

Set NDoc = NMailDb.CREATEDOCUMENT
With NDoc
.Form = "Memo"
.SendTo = recipientsArray
.Subject = Subject
.body = BodyText
.SAVEMESSAGEONSEND = True 'Save sent mail?

If TypeName(Attachments) = "String" Then

'Attachments argument is a comma-separated string of filenames

AttachmentsArray = Split(Attachments, ",")

ElseIf TypeName(Attachments) = "Variant()" Then

'Attachments argument is an array of filename strings

AttachmentsArray = Attachments

End If

'For each attachment, create a rich text item with unique name and an associated embedded object

For i = LBound(AttachmentsArray) To UBound(AttachmentsArray)

Set NRichTextItem = .CREATERICHTEXTITEM("Attachment_" & i)

If Dir(AttachmentsArray(i)) <> "" Then
'Function EMBEDOBJECT(TYPE As Integer, CLASS As String, SOURCE As String, [OBJECTNAME])
Set NEmbeddedObj = NRichTextItem.EMBEDOBJECT(EMBED_ATTACHMENT, "", AttachmentsArray(i))
Else
MsgBox "Attachment file not found: " & AttachmentsArray(i)
End If
Next

'Send the document

'SEND(ATTACHFORM As Integer, [RECIPIENTS])
.SEND False

'SAVE(FORCE As Integer, MAKERESPONSE As Integer, [MARKREAD]) As Integer
'MARKREAD: True - the document subject is set to black (read) in the Sent folder; False - red (unread)
.Save True, True, False

End With

'Clean up

Set NMailDb = Nothing
Set NDoc = Nothing
Set NRichTextItem = Nothing
Set NSession = Nothing
Set NEmbeddedObj = Nothing

End Sub

 

[TheCman81]

Thanks for responding, I don't need to attached the file from the directory, I just need to attach the file the macro is being run on?

Thanks

 

[george hart]

No problem

In that case this work fine work:

Public Sub Notes_Email_Workbook()

Dim recipients As Variant
Dim emailBodyText As String
recipients = "Add recipient"
emailBodyText = "Please see attached bla bla bla"

Create_and_Send_Notes_Email "Please see attached bla bla " & Now, recipients, emailBodyText, ThisWorkbook.FullName

End Sub

 

[TheCman81]

I get a Complie Error, Sub or Function not defined

 

[george hart]

Copy this to the sheet in which the code resides:

Private Sub Create_and_Send_Notes_Email(Subject As String, recipientsArray As Variant, BodyText As String, Attachments As Variant)
Const EMBED_ATTACHMENT As Long = 1454

'Declare objects for Lotus Notes automation
Dim NSession As Object 'NotesSession
Dim NMailDb As Object 'NotesDatabase
Dim NDoc As Object 'NOTESDOCUMENT - the mail document itself
Dim NRichTextItem As Object 'The attachment rich text file object
Dim NEmbeddedObj As Object 'The embedded object (Attachment)
Dim AttachmentsArray As Variant
Dim I As Integer

'Start a Notes session

Set NSession = CreateObject("Notes.NotesSession") 'Lotus Notes Automation Classes (OLE)
Set NMailDb = NSession.GETDATABASE("", "") 'uses the default .nsf database

If Not NMailDb.IsOpen Then
NMailDb.OPENMAIL
End If
'Create a new mail document

Set NDoc = NMailDb.CREATEDOCUMENT
With NDoc
.Form = "Memo"
.SendTo = recipientsArray
.Subject = Subject
.body = BodyText
.SAVEMESSAGEONSEND = True 'Save sent mail?

If TypeName(Attachments) = "String" Then

'Attachments argument is a comma-separated string of filenames

AttachmentsArray = Split(Attachments, ",")

ElseIf TypeName(Attachments) = "Variant()" Then

'Attachments argument is an array of filename strings
AttachmentsArray = Attachments

End If

'For each attachment, create a rich text item with unique name and an associated embedded object

For I = LBound(AttachmentsArray) To UBound(AttachmentsArray)

Set NRichTextItem = .CREATERICHTEXTITEM("Attachment_" & I)

If Dir(AttachmentsArray(I)) <> "" Then
'Function EMBEDOBJECT(TYPE As Integer, CLASS As String, SOURCE As String, [OBJECTNAME])
Set NEmbeddedObj = NRichTextItem.EMBEDOBJECT(EMBED_ATTACHMENT, "", AttachmentsArray(I))
Else
MsgBox "Attachment file not found: " & AttachmentsArray(I)
End If
Next

'Send the document

'SEND(ATTACHFORM As Integer, [RECIPIENTS])
.send False

'SAVE(FORCE As Integer, MAKERESPONSE As Integer, [MARKREAD]) As Integer
'MARKREAD: True - the document subject is set to black (read) in the Sent folder; False - red (unread)
.Save True, True, False

End With

'Clean up

Set NMailDb = Nothing
Set NDoc = Nothing
Set NRichTextItem = Nothing
Set NSession = Nothing
Set NEmbeddedObj = Nothing

End Sub

 

[TheCman81]

All this is doing is sending the email, it isn't attaching the workbook

 

[TheCman81]

Just for an update, I adapted a vba script which I found here:

Mail from Excel and make/mail PDF files (Windows)