I am trying to send 7 different emails in this sub, the sendnotes sub is not recognizing my GOLOOP value
Could somebody help me out please
As you can see I stole this code from this site. You all are terrific!
Sub SendFiles()
Sheets("Menu").Select
Range("C23").Select
Filename = ActiveCell.Value
GoLoop = "1"
SendNotesMail
GoLoop = "2"
SendNotesMail
GoLoop = "3"
SendNotesMail
GoLoop = "4"
SendNotesMail
GoLoop = "5"
SendNotesMail
GoLoop = "6"
SendNotesMail
GoLoop = "7"
SendNotesMail
End Sub
Sub SendNotesMail()
Stop
Dim Maildb As Object, MailDoc As Object, AttachMe As Object, Session As Object
Dim UserName As String, MaildDbName As String
Dim EmbedObj1 As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ":.nsf"
Set Maildb = Session.GetDataBase(vbNullString, MailDbName)
If Not Maildb.IsOpen Then Maildb.OpenMail
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Select Case (GoLoop)
Case "1"
Mail.Doc.SendTo = "email@email.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMAL " & Filename & ".xls", "Attachment")
Case "2"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMBC " & Filename & ".xls", "Attachment")
Case "3"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMC " & Filename & ".xls", "Attachment")
Case "4"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMCA " & Filename & ".xls", "Attachment")
Case "5"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMI " & Filename & ".xls", "Attachment")
Case "6"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMK " & Filename & ".xls", "Attachment")
Case "7"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMWV " & Filename & ".xls", "Attachment")
End Select
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo ErrorCheck
Call MailDoc.Send(False)
Set EmbedObj = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing
Set Maildb = Nothing: Set Session = Nothing
Exit Sub
ErrorCheck:
Set EmbedOfj1 = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing
Set Maildb = Nothing: Set Session = Nothing
End Sub
Edited by NPO: email address substituted
Could somebody help me out please
As you can see I stole this code from this site. You all are terrific!
Sub SendFiles()
Sheets("Menu").Select
Range("C23").Select
Filename = ActiveCell.Value
GoLoop = "1"
SendNotesMail
GoLoop = "2"
SendNotesMail
GoLoop = "3"
SendNotesMail
GoLoop = "4"
SendNotesMail
GoLoop = "5"
SendNotesMail
GoLoop = "6"
SendNotesMail
GoLoop = "7"
SendNotesMail
End Sub
Sub SendNotesMail()
Stop
Dim Maildb As Object, MailDoc As Object, AttachMe As Object, Session As Object
Dim UserName As String, MaildDbName As String
Dim EmbedObj1 As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ":.nsf"
Set Maildb = Session.GetDataBase(vbNullString, MailDbName)
If Not Maildb.IsOpen Then Maildb.OpenMail
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Select Case (GoLoop)
Case "1"
Mail.Doc.SendTo = "email@email.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMAL " & Filename & ".xls", "Attachment")
Case "2"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMBC " & Filename & ".xls", "Attachment")
Case "3"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMC " & Filename & ".xls", "Attachment")
Case "4"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMCA " & Filename & ".xls", "Attachment")
Case "5"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMI " & Filename & ".xls", "Attachment")
Case "6"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMK " & Filename & ".xls", "Attachment")
Case "7"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "
Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMWV " & Filename & ".xls", "Attachment")
End Select
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo ErrorCheck
Call MailDoc.Send(False)
Set EmbedObj = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing
Set Maildb = Nothing: Set Session = Nothing
Exit Sub
ErrorCheck:
Set EmbedOfj1 = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing
Set Maildb = Nothing: Set Session = Nothing
End Sub
Edited by NPO: email address substituted