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

ericpny2

New Member
Joined
Jun 4, 2011
Messages
33
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
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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)
 
Upvote 0
Thanks for the help and guidance. Much appreciated. But i still cannot get the file attached. not sure why.
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top