23izkool
02-23-2011, 04:24 PM
Hi ppl,
I have been at this problem for ages....haiz...1 last part to go & did not realize it can be so hard...These are what I need to do & hope someone can help!!!
WHAT THE CURRENT MACRO DOES
I have an excel spreadsheet which records (1) Email Address in Col A, (2) Subject in Col B, (3) Email body in Col C
Activating the macro sends out an the same email (in Col C) to all recipients in Col A by running through a For Loop
The purpose of this is to send out mass LN to 'x' number of recipients such that each of this recipient only sees himself as the recipient instead of himself as part of an entire list - The body of the email will need to be customized uniquely to each recipient --> therefore the need to run this macroBUT until the signature file is attached, CC enabled, email recored in 'Sent Items', we are unable to release the usage of this macro...arghh...more details below:
PROBLEMS - WHAT MACRO DOES NOT DO
I need to append Signature File at the bottom of each email that is sent out
This Signature File is like our name 'Thanks and Best Regards, Daniel, VP' together with a banner / picture file
I have saved this Signature as a 'Web Page, Filtered (*.htm; *.html)
After that, I go to Lotus Notes --> Tools --> Preferences --> Select Signature --> Check HTML or Image File --> Select the htm file from Step 2 above
Also, the current macro is unable to CC another person despite me writing in the code
I also need the macro to record an entry in 'Sent Mails' for reference sakeTHE CURRENT MACRO CODE (WITH HELP MANY OTHER VBA GURUS ONLINE!!!)
Sub SendLN()
Dim Maildb As Object
Dim Maildoc As Object
Dim Body As Object
Dim Session As Object
Dim i As Integer
Dim objNotesDocument As Object
Dim objNotesField As Object
For i = 2 To 3
' Because the 1st row is the heading
Set Session = CreateObject("Lotus.NotesSession")
CallSession.Initialize ("password of person sending out LN")
Set Maildb = Session.GETDATABASE("", "C:\lotus notes id of user.nsf")
If Not Maildb.IsOpen = True Then Call Maildb.Open
Set Maildoc = Maildb.CREATEDDOCUMENT
Call Maildoc.REPLACEITEMVALUE("Form", "Memo")
Call Maildoc.REPLACEITEMVALUE("SendTo", Cells(i, 1).Value)
Call Maildoc.REPLACEITEMVALUE("Subject", Cells(i, 2).Value)
Set objNotesSession = CreateObject("Notes.NotesSession")
Set objNotesMailFile = objNotesSession.GETDATABASE(",")
objNotesMailFile.OPENMAIL
Set objNotesDocument = objNotesMailFile.CREATEDDOCUMENT
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EmailCCTo)
EmailCCTo = "person whom is supposed to be CCed"
Set Body = Maildoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT(Cells(2, 3).Value)
Call Body.ADDNEWLINE(2)
Call Body.EMBEDOBJECT(1454, "", "C:\directory to store the files" & Cells(i, 4).Value & ".doc", "Attachment")
Maildoc.SAVEMESSAGEONSEND = True
Call Maildoc.REPLACEITEMVALUE("PosteDate", Now())
Call Maildoc.SEND(False)
Set Maildb = Nothing
Set Maildoc = Nothing
Set Body = Nothing
Set Session = Nothing
Next i
End Sub
MY CURRENT FINDINGS SO FAR
The htm file is a rich text file that we need to somehow get the VBA to transport into Lotus Notes but I am totally stuck as cant' seem to find any posting on this
The reason why I need this is because I also have a picture (a banner to be shown below my name at the bottom of every email I send out )--> so I cannot subsitute by just typing it as part of the body text. I also need to colour & bold my name in red
I am not restricting to using signature --> if there are other good subsitutes that give the same result (a lot of times, some VBA geniuses will come up with an ingenious method...hope I can get some help here!)
The 'CC' function & 'Sent Items' are actually written into the code above but does not seem to workHope someone has some idea on this!! Thanks!!
I have been at this problem for ages....haiz...1 last part to go & did not realize it can be so hard...These are what I need to do & hope someone can help!!!
WHAT THE CURRENT MACRO DOES
I have an excel spreadsheet which records (1) Email Address in Col A, (2) Subject in Col B, (3) Email body in Col C
Activating the macro sends out an the same email (in Col C) to all recipients in Col A by running through a For Loop
The purpose of this is to send out mass LN to 'x' number of recipients such that each of this recipient only sees himself as the recipient instead of himself as part of an entire list - The body of the email will need to be customized uniquely to each recipient --> therefore the need to run this macroBUT until the signature file is attached, CC enabled, email recored in 'Sent Items', we are unable to release the usage of this macro...arghh...more details below:
PROBLEMS - WHAT MACRO DOES NOT DO
I need to append Signature File at the bottom of each email that is sent out
This Signature File is like our name 'Thanks and Best Regards, Daniel, VP' together with a banner / picture file
I have saved this Signature as a 'Web Page, Filtered (*.htm; *.html)
After that, I go to Lotus Notes --> Tools --> Preferences --> Select Signature --> Check HTML or Image File --> Select the htm file from Step 2 above
Also, the current macro is unable to CC another person despite me writing in the code
I also need the macro to record an entry in 'Sent Mails' for reference sakeTHE CURRENT MACRO CODE (WITH HELP MANY OTHER VBA GURUS ONLINE!!!)
Sub SendLN()
Dim Maildb As Object
Dim Maildoc As Object
Dim Body As Object
Dim Session As Object
Dim i As Integer
Dim objNotesDocument As Object
Dim objNotesField As Object
For i = 2 To 3
' Because the 1st row is the heading
Set Session = CreateObject("Lotus.NotesSession")
CallSession.Initialize ("password of person sending out LN")
Set Maildb = Session.GETDATABASE("", "C:\lotus notes id of user.nsf")
If Not Maildb.IsOpen = True Then Call Maildb.Open
Set Maildoc = Maildb.CREATEDDOCUMENT
Call Maildoc.REPLACEITEMVALUE("Form", "Memo")
Call Maildoc.REPLACEITEMVALUE("SendTo", Cells(i, 1).Value)
Call Maildoc.REPLACEITEMVALUE("Subject", Cells(i, 2).Value)
Set objNotesSession = CreateObject("Notes.NotesSession")
Set objNotesMailFile = objNotesSession.GETDATABASE(",")
objNotesMailFile.OPENMAIL
Set objNotesDocument = objNotesMailFile.CREATEDDOCUMENT
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EmailCCTo)
EmailCCTo = "person whom is supposed to be CCed"
Set Body = Maildoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT(Cells(2, 3).Value)
Call Body.ADDNEWLINE(2)
Call Body.EMBEDOBJECT(1454, "", "C:\directory to store the files" & Cells(i, 4).Value & ".doc", "Attachment")
Maildoc.SAVEMESSAGEONSEND = True
Call Maildoc.REPLACEITEMVALUE("PosteDate", Now())
Call Maildoc.SEND(False)
Set Maildb = Nothing
Set Maildoc = Nothing
Set Body = Nothing
Set Session = Nothing
Next i
End Sub
MY CURRENT FINDINGS SO FAR
The htm file is a rich text file that we need to somehow get the VBA to transport into Lotus Notes but I am totally stuck as cant' seem to find any posting on this
The reason why I need this is because I also have a picture (a banner to be shown below my name at the bottom of every email I send out )--> so I cannot subsitute by just typing it as part of the body text. I also need to colour & bold my name in red
I am not restricting to using signature --> if there are other good subsitutes that give the same result (a lot of times, some VBA geniuses will come up with an ingenious method...hope I can get some help here!)
The 'CC' function & 'Sent Items' are actually written into the code above but does not seem to workHope someone has some idea on this!! Thanks!!