excelpinto
Board Regular
- Joined
- Feb 14, 2008
- Messages
- 53
Hey all,
I have some vba code that will go through and generate emails for each row in a spreadsheet. One of the columns in the spreadsheet is a hyperlink but its just bringing over the "Friendly Name" in the notes email. I want it to bring over the clickable hyperlink. Any ideas?
<code>
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = "mail\" & Mid$(UserName, 4, InStr(1, UserName, " ") - 4) & Mid$(UserName, _
InStr(1, UserName, " ") + 1, InStr(1, UserName, "/") - InStr(1, UserName, " ") - 1) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else: Maildb.OPENMAIL
End If
For r = 2 To Range("b1").End(xlDown).Row
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Email = Cells(r, 17)
'attachment = Cells(r, 4)
Subj = "ACTION NEEDED - Past Due Task: " & Cells(r, 2)
Msg = ""
Msg = Msg & "Line 1" & vbCrLf
Msg = Msg & "Line 2" & vbCrLf
Msg = Msg & Cells(r, 11) & vbCrLf <- this is where the Hyperlink is
MailDoc.SendTo = Email
MailDoc.Subject = Subj
MailDoc.Body = Msg
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
'Set up the embedded object and attachment and attach it
If attachment <> "" Then
Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = attachME.EmbedObject(1454, "", attachment, "Attachment")
End If
'Send the document
'MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
Call MailDoc.Send(False)
Next r
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
</code>
I have some vba code that will go through and generate emails for each row in a spreadsheet. One of the columns in the spreadsheet is a hyperlink but its just bringing over the "Friendly Name" in the notes email. I want it to bring over the clickable hyperlink. Any ideas?
<code>
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = "mail\" & Mid$(UserName, 4, InStr(1, UserName, " ") - 4) & Mid$(UserName, _
InStr(1, UserName, " ") + 1, InStr(1, UserName, "/") - InStr(1, UserName, " ") - 1) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else: Maildb.OPENMAIL
End If
For r = 2 To Range("b1").End(xlDown).Row
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Email = Cells(r, 17)
'attachment = Cells(r, 4)
Subj = "ACTION NEEDED - Past Due Task: " & Cells(r, 2)
Msg = ""
Msg = Msg & "Line 1" & vbCrLf
Msg = Msg & "Line 2" & vbCrLf
Msg = Msg & Cells(r, 11) & vbCrLf <- this is where the Hyperlink is
MailDoc.SendTo = Email
MailDoc.Subject = Subj
MailDoc.Body = Msg
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
'Set up the embedded object and attachment and attach it
If attachment <> "" Then
Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = attachME.EmbedObject(1454, "", attachment, "Attachment")
End If
'Send the document
'MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
Call MailDoc.Send(False)
Next r
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
</code>