Send email through Lotus - selecting hyperlink PDF file from excel sheet

[ericpny2]

So i am sending an email through lotus notes and the code that i currently have doesn't correctly pick the file. It is a pdf file that is contained in a cell by hyperlink (cell F19). there will be mupltiple hyperlinks so i want it to pick the link that is highlighted in the sheet. please help me out

here is the code:

Sub EmailFile()
' Declare Variables for file and macro setup
Dim UserName As String, ccrecipient As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object
' 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"
MailDoc.SendTo = "Recipients"
ccrecipient = "ccRecipients"
MailDoc.Subject = "Subject"
MailDoc.Body = "BODY"
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = False
attachment1 = "" ' Required File Name
If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.EmbedObject(1454, "attachment1", "=F19") 'Required File Name
On Error Resume Next
End If
Set workspace = CreateObject("Notes.NotesUIWorkspace")
Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

 

[Ralajer]

For starters I don't have Lotus Notes so the below is my best guess implementation based on documentation. If I understanding your requirement you want to attach the file that has a hyperlink in F19 to the email message.

Try this code.

Sub EmailFile()
    ' Declare Variables for file and macro setup
    Dim UserName As String, ccrecipient As String
    Dim MailDbName As String
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim AttachME As Object
    Dim Session As Object
    Dim EmbedObj1 As Object
'// ———————————————————————————————————————————————————————————————————————————
    Dim attachRng As Range
    Dim attachPath As String
    Dim attachName As String
'// ———————————————————————————————————————————————————————————————————————————
    
    ' 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"
    MailDoc.SendTo = "Recipients"
    ccrecipient = "ccRecipients"
    MailDoc.Subject = "Subject"
    MailDoc.Body = "BODY"
    
    ' Select Workbook to Attach to E-Mail
    MailDoc.SaveMessageOnSend = False
'// ———————————————————————————————————————————————————————————————————————————
    Set attachRng = Range("F19")
    attachPath = attachRng.Hyperlinks(1).Address
    attachName = attachRng.Value
    
    If attachPath <> "" Then
        On Error Resume Next
        Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
        Set EmbedObj1 = AttachME.EmbedObject(1454, "", attachPath, attachName) 'Required File Name
        On Error Resume Next
    End If
'// ———————————————————————————————————————————————————————————————————————————

    Set workspace = CreateObject("Notes.NotesUIWorkspace")
    
    Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")
    
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing

errorhandler1:
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Also please do not post the same question (http://www.mrexcel.com/forum/showthread.php?p=2762915) if you haven't gotten an answer. If after a reasonable amount of time you haven't gotten an answer reply to your question which will bump it to the top of the thread list. (See posting tips)

 

[ericpny2]

Thanks for the help and guidance. Much appreciated. But i still cannot get the file attached. not sure why.

 

[Ralajer]

I am not going to be able to help too much as I can't test it myself but I might be able to get you started.

Run this and let me know what the message boxes reports

Sub CheckVal()   
    Dim attachRng As Range
    Dim attachPath As String
    Dim attachName As String
    
    Set attachRng = Range("F19")
    attachPath = attachRng.Hyperlinks(1).Address
    attachName = attachRng.Value

    MsgBox "The Path is - " attachPath

    MsgBox "The Name is - " attachName

End Sub