Email through Lotus Notes After cell change

[DasBurgers]

Excel gods I need some help.

I am looking to build a macro that can automatically email a recipient upon their name being selected from a drop down list. It designed to be for a warehouse, so when new packages come in we can scan them in and have excel send an email out once our receiving team determines who it is for. With their being multiple recipients, I would like the code to have an IF type function that choosing the correct recipient. the Column titles "Recipient/Dept" is where the dropdown list is located.

In my head it would be ideal for the code to just utilize an open Lotus Notes Session and use that to send the email. Attached is the document with what I have thus far.
Any and all help is greatly appreciated.

-DasBurgers

Warehouse barcode test.xlsm

[estratosphere]

Hi DasBurgers,

I would suggest taking a look at Ron DeBruin's website - his site contains loads of great mail macros for both Lotus Notes and Outlook.
There will definitely be something there you can use/adapt for your purposes.

http://www.rondebruin.nl/win/s1/notes/notes.htm

If you are still stuck once you have had a look at this page, I have some experience with using Ron's code for Notes and can probably pull together some code for you.

[DasBurgers]

Perfect!! Thanks a bunch for the info and will give the website a look to make another few attempts at it. If I have issues I'll come back here. Thanks again for your quick response.

[JOHN H. DAVIS]

See if this is something you can work with. You'll have to modify it for your needs. When a selection is made from the dropdown an email is sent.

[DasBurgers]

^^ Works absolutely fantastic!! Just one more question.

I am looking to have a selected range in the body of the email with each email sent. Not just a set range but the data, to the left, adjacent to the name selected in the drop down list.

I am assuming it is going to be something along these lines, with some type of IF function or OFFSET to select the respective row and columns to the left of the name.

rnBody.Copy
Set Data = New DataObject
Data.GetFromClipboard

Again your help is greatly appreciated. Rep for all who help!!

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[DasBurgers]

perfect thanks so much....apologizies again for the onslaught of PMs lol

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[DasBurgers]

Will do. Running into an issue. I have added another column for the carrier and using your suggested MailDoc.Body code works for the Carrier but not the Tracking number. It uses the date as the tracking number, seems as though it is looking the column over 3 for both but I checked and it is set to -2 & -3.

Does this issue have to do with the time stamp sub I have in the beginning?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
x = Sheets("Recipients").Range("A" & Rows.Count).End(3).Row
If Target.Column = 2 And Target.Offset(0, 2).Value = "" Then
Target.Offset(0, -1) = Format(Now(), "mm/dd/yy")
End If
If Not Intersect(Target, Columns(4)) Is Nothing Then
Target.Offset(, 1).Formula = "=VLOOKUP(D" & Target.Row & ",Recipients!$A$2:$B$" & x & ",2,false)"
End If

Dim UserName As String
Dim MailDbName As String
Dim Recipient As Variant
Dim ccRecipient As String
Dim Attachment1 As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object
Dim stSignature As String

With Application
.ScreenUpdating = False
.DisplayAlerts = False

' Open and locate current LOTUS NOTES User

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
Else
Maildb.OPENMAIL
End If

' Create New Mail and Address Title Handlers

Set MailDoc = Maildb.CREATEDOCUMENT

MailDoc.Form = "Memo"

stSignature = Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)


' Select range of e-mail addresses
Recipient = Array("", Target.Offset(, 1).Value)
MailDoc.SendTo = Recipient
MailDoc.Subject = "Package Received Under Your Name/Dept"
MailDoc.Body = "The Date is " & Target.Offset(, -3) & vbCrLf & "The Tracking No. is " & Target(, -2) & vbCrLf & "The Carrier is " & Target(, -1) & vbCrLf & vbCrLf & stSignature

' Select Workbook to Attach to E-Mail

MailDoc.SaveMessageOnSend = True


MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing

.ScreenUpdating = True
.DisplayAlerts = True
End With

errorhandler1:

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub

[DasBurgers]

Ignore^^^^ Was able to figure it out. However, my msgbox is popping up twice everytime. What could be the cause of this?

[JOHN H. DAVIS]

Place your msgbox above errorhandler1:

[DasBurgers]

still apearing twice

[JOHN H. DAVIS]

Above this line:

Please Login or Register  to view this content.

[DasBurgers]

still occuring

Warehouse barcode test.xlsm

[DasBurgers]

Being new to the forum, I really really appreciate all your help John H. Davis. Whether assistance with the coding itself or informing me of the rules on the forums, it is all greatly appreciated, especially your paitence with me.

[JOHN H. DAVIS]

I can't figure it out either. Perhaps someone else may be able to help.

[JOHN H. DAVIS]

Being new to the forum, I really really appreciate all your help John H. Davis. Whether assistance with the coding itself or informing me of the rules on the forums, it is all greatly appreciated, especially your paitence with me.

You're welcome. The Forum is where I picked up most of my knowledge about VBA. Sorry, I can't help further with the double msgbox. I just don't have any idea.

[JOHN H. DAVIS]

Place it here and it works only once.

Please Login or Register  to view this content.

[DasBurgers]

You're welcome. The Forum is where I picked up most of my knowledge about VBA. Sorry, I can't help further with the double msgbox. I just don't have any idea.

No worries, you have been a tremendous help already. I hope to learn as much as you through this forum!!!

[DasBurgers]

Place it here and it works only once.




If Not Intersect(Target, Columns(4)) Is Nothing Then
Target.Offset(, 1).Formula = "=VLOOKUP(D" & Target.Row & ",Recipients!$A$2:$B$" & x & ",2,false)"
MsgBox "The e-mail has successfully been created and distributed."
End If

PERFECT!!!! Answered all of my questions beautifuly. Thanks again for taking the time out to help!!