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
1. I have an excel spreadsheet which records (1) Email Address in Col A, (2) Subject in Col B, (3) Email body in Col C
2. Activating the macro sends out an the same email (in Col C) to all recipients in Col A by running through a For Loop
3. 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 macro
BUT until the signature file is attached, we are unable to release the usage of this macro...arghh...more details below:
PROBLEMS - WHAT MACRO DOES NOT DO
1. I need to append Signature File at the bottom of each email that is sent out
1. This Signature File is like our name 'Thanks and Best Regards, Daniel, VP' together with a banner / picture file
2. I have saved this Signature as a 'Web Page, Filtered (*.htm; *.html)
3. After that, I go to Lotus Notes --> Tools --> Preferences --> Select Signature --> Check HTML or Image File --> Select the htm file from Step 2 above
2. Also, the current macro is unable to record the sent mails in 'Sent Mails' unless i change a line in the macro to "Set Maildb = Session.GETDATABASE("", "") such that the user just clicks the macro and will open the default LN that is open. BUT what I am doing now is the get a 3rd party 'y' to send out the mass LN in the name of the person 'X'. For this, i switched the lotus notes ID to person 'X' & changed the line in the macro to "Set Maildb = Session.GETDATABASE("", "C:\archive file of person X.nsf"). Then I'm able to send out as 'y' BUT the mails do not get recorded in my sent mails
THE 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
1. 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
2. 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
3. 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!)
4. The 'Sent Items' are actually written into the code above but does not seem to work if I am using the 3rd party method
Hope someone has some idea on this!! Especially the signature portion!!! It's a big headache & I do not seem to find any standard coding like the attachment 1454 method.. I hope to write the macro in such a way that it will pick up the html file (I can put them in a location easy to pick up). Hope some1 can help 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
1. I have an excel spreadsheet which records (1) Email Address in Col A, (2) Subject in Col B, (3) Email body in Col C
2. Activating the macro sends out an the same email (in Col C) to all recipients in Col A by running through a For Loop
3. 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 macro
BUT until the signature file is attached, we are unable to release the usage of this macro...arghh...more details below:
PROBLEMS - WHAT MACRO DOES NOT DO
1. I need to append Signature File at the bottom of each email that is sent out
1. This Signature File is like our name 'Thanks and Best Regards, Daniel, VP' together with a banner / picture file
2. I have saved this Signature as a 'Web Page, Filtered (*.htm; *.html)
3. After that, I go to Lotus Notes --> Tools --> Preferences --> Select Signature --> Check HTML or Image File --> Select the htm file from Step 2 above
2. Also, the current macro is unable to record the sent mails in 'Sent Mails' unless i change a line in the macro to "Set Maildb = Session.GETDATABASE("", "") such that the user just clicks the macro and will open the default LN that is open. BUT what I am doing now is the get a 3rd party 'y' to send out the mass LN in the name of the person 'X'. For this, i switched the lotus notes ID to person 'X' & changed the line in the macro to "Set Maildb = Session.GETDATABASE("", "C:\archive file of person X.nsf"). Then I'm able to send out as 'y' BUT the mails do not get recorded in my sent mails
THE 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
1. 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
2. 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
3. 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!)
4. The 'Sent Items' are actually written into the code above but does not seem to work if I am using the 3rd party method
Hope someone has some idea on this!! Especially the signature portion!!! It's a big headache & I do not seem to find any standard coding like the attachment 1454 method.. I hope to write the macro in such a way that it will pick up the html file (I can put them in a location easy to pick up). Hope some1 can help on this. .Thanks!!