I have a spread sheet and i want to send a email to a persons email add if the date of a proposed action is 3 days away from being complete
Example file attachedco2.xlsm
I have a spread sheet and i want to send a email to a persons email add if the date of a proposed action is 3 days away from being complete
Example file attachedco2.xlsm
Can anyone help with a macro
I think all you need to do is change this line:
' Select range of e-mail addresses Recipient = Range("F3").Value '<------------
Spot on thanks was a long night missed that one point
Glad to help out.
1 more question if i wanted to do this line by line with different emails can this be done
Something like this may work. Untested though.
Will your recipient always be Range("F3") or will it also be in the loop? If so then that line would have to change as well. Range("F" & i).valueSub jt1672() Dim UserName As String Dim MailDbName As String Dim Recipient As Variant Dim Maildb As Object Dim MailDoc As Object Dim AttachME As Object Dim Session As Object Dim stSignature As String Dim i As Long With Application .ScreenUpdating = False .DisplayAlerts = False End With For i = 2 To Range("A" & Rows.count).End(3)(2).Row If Range("d" & i) < Range("e" & i) Then 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 = Range("F3").Value '<------------ MailDoc.SendTo = Recipient MailDoc.subject = "STOCK REQUISITION REQUIRED" '<--------------- MailDoc.Body = "Your inventory is low, please order your stocks today." & vbCrLf & vbCrLf & stSignature MailDoc.SaveMessageOnSend = True MailDoc.PostedDate = Now() On Error GoTo errorhandler1 MailDoc.SEND 0, Recipient Set Maildb = Nothing Set MailDoc = Nothing Set Session = Nothing With Application .ScreenUpdating = True .DisplayAlerts = True End With errorhandler1: Set Maildb = Nothing Set MailDoc = Nothing Set Session = Nothing End If Next i End Sub
Ok have attached a file an need this
if Cell V7 8 9 10 an so on is less than H1 i need it to send a mail in lotus notes using email from cell O7 8 and so on can you help. some code is in sheet but now stuck
test.xlsm
Maybe:
Sub jt1672() Dim UserName As String Dim MailDbName As String Dim Recipient As Variant Dim Maildb As Object Dim MailDoc As Object Dim AttachME As Object Dim Session As Object Dim stSignature As String Dim i As Long With Application .ScreenUpdating = False .DisplayAlerts = False End With For i = 7 To Range("A" & Rows.count).End(3)(2).Row Step -1 If Range("V" & i).Value < Range("h1").Value Then 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 = Range("o" & i).Value '<------------ MailDoc.SendTo = Recipient MailDoc.subject = "STOCK REQUISITION REQUIRED" '<--------------- MailDoc.Body = "Your inventory is low, please order your stocks today." & vbCrLf & vbCrLf & stSignature MailDoc.SaveMessageOnSend = True MailDoc.PostedDate = Now() On Error GoTo errorhandler1 MailDoc.SEND 0, Recipient Set Maildb = Nothing Set MailDoc = Nothing Set Session = Nothing With Application .ScreenUpdating = True .DisplayAlerts = True End With errorhandler1: Set Maildb = Nothing Set MailDoc = Nothing Set Session = Nothing End If Next i End Sub
spot on thanks
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks