vba macro in Excel

[Jodie]

Hi,

I am creating macro code that would automatically send my
excel file (after editing) to an Email using Lotus Notes
R5 with the following parameters: SERVER, FROM, TO, CC,
BCC, SUBJECT, MESSAGES and ATTACHMENT, Can anyone give me
some tips or ideas how to figure out this one.

Thanking you in advance.
Jodie

 

[Bill Li]

Hi Jodie,

You can record the macro and then modify it.
Following is a example

ActiveWorkbook.SendMail recipients:="Jean Selva"

Best Regards

Bill

 

[Andy Wiggins]

Here's a VBA function to mail files using Lotus Notes (watch out for line
wrapping).

''
***************************************************************************
'' Purpose : Send a file as an attachment through Notes
'' Written : 04-Jun-2003 by Andy Wiggins, Byg Software Limited
''
Function SendMail(EMailSendTo As String, EmailSubject As String)
Dim EMailCCTo
Dim EMailBCCTo

On Error GoTo SendMailError

EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional

''Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")

''Establish Connection to Mail File
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
''Open Mail
objNotesMailFile.OPENMAIL

''Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT

''Create 'Subject Field'
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject",
EmailSubject)

''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo",
EMailSendTo)

''Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo",
EMailCCTo)

''Create 'Blind Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo",
EMailBCCTo)

''Create 'Body' of memo
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")

With objNotesField
.APPENDTEXT "This e-mail is generated by an automated process."
.ADDNEWLINE 1
.APPENDTEXT "Please follow established contact procedures should you
have any questions."
.ADDNEWLINE 2
End With

''Attach the file --1454 indicate a file attachment
objNotesField = objNotesField.EMBEDOBJECT(1454, "", EmailSubject)
'' objNotesField = objNotesField.EMBEDOBJECT(1454, "",
ActiveWorkbook.FullName)

''Send the e-mail
objNotesDocument.Send (0)

''Release storage
Set objNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing

''Set return code
SendMail = True

Exit Function

SendMailError:
Dim Msg
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

SendMail = False

End Function

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"

 

[mcm]

Although i haven't tried your code, will it also work on lotus notes
4.6?

 

[Jon Peltier]

Andy -

My client uses Lotus Notes, and I don't have access to it to test my
code. When I establish a connection to the mail file, you show empty
strings for SERVER and FILE in the code:

''Establish Connection to Mail File
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")

Can I actually leave this as is, or do I need to get my client to fill
these in for me? I want to send him a sample to test, which sends me a
message so I know it worked.

Thanks,

- Jon

 

[Andy Wiggins]

Hi Jon,

Sorry I can't answer your specific questions :-(

I don't have Notes either. What I posted is a version of a hack that has, so
far, worked for me on three client sites (with a little tinkering here and
there).

The version I actually posted was for a client I worked with earlier this
year. They had tried several things they found on the web without success
and then we tried this code.

In context, this code was part of a managment information suite. I was
producing workbooks detailing the client's sales relationships with around
150 of their clients. These files were then zipped up and mailed through
their Notes system to the account managers. All this was automated through
an Excel interface.

What I posted is the code I used and that line:
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
is how it appeared in the live version.

HTH
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "The Excel Auditor" and "Byg Tools for VBA"