Update Registry key via VBA/excel to chnage default E-mail to Lotus Notes

[Misty Shay]

Hey there,

I'm very new to VBA however, have found great stuff on this site and have been able to create a button to automatically send my excel data via e-mail. However this feature will only works if the user has their default e-mail in Internet explorer set to "Louts Notes" as that's what our Company uses. Therefore, I'd like to create a module in VBA that will automatically set the default to Lotus Notes when they open my workbook. I have the run.application command to start on open, but need help with the registry re-set. Has anyone done this before? I'd appreciate any help you could offer.

[lucas]

There are several entries in the knowledgbase that deal with emailing workbooks, worksheets, etc. using other than outlook or default program. I don't think it's necessary to change their default program to do this.

http://vbaexpress.com/kb/default.php?action=search

enter email in the keywords box and select Excel from the applications list and hit search.

[Brandtrock]

Amend registry KB (http://vbaexpress.com/kb/getarticle.php?kb_id=208)


Using MAPI to e-mail KB (http://vbaexpress.com/kb/getarticle.php?kb_id=311)


Here's a couple of items to look over.

Regards,

[Misty Shay]

When I run my file without changing the registry I get "no profiles have been created errors", once I manually change it on my workstation, It works. So I know that the Registry key is a problem. However, you bring up a good point, maybe I'm using an e-mail approach that references the default e-mail within the registry and there's another approach that may not require it. Here's the code I'm using, please advise if you (or anyone) knows of a better approach. . .

Sub Send1Sheet_ActiveWorkbook()
'Create a new Workbook Containing 1 Sheet (left most) _
and sends as attachment.

ThisWorkbook.Sheets(1).Copy


With ActiveWorkbook
.SendMail Recipients:= mail address, _
Subject:="Notification If Contract Vehicle is Approved or Denied " & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With

End Sub


Thanks!

[lucas]

Your code reflects that your still using the default email program, correct? For alternatives see the links listed above, especially the one Brantrock lists for mapi to send email.

[Misty Shay]

Ok I removed my code and entered the MAPI code Repointing the e-mail address to me. . .However when I run the Macro I get an "Error Sending: -2147467259" message. I also tried this in the test file and it did the same error. Not sure if I need to enable something prior to running the MAPI Code. Please advise if you know how to troubleshoot this.

Thanks in advance!

[lucas]

Only thing I can see is if you didn't uncomment this line and add the required info....otherwise it works for me.
'Logon: User = "" and Password = ""

[ironj32]

How do i initiate the email action from above. do i connect it to a cmdbutton?

Sub cmdTest_click()
'Create a new Workbook Containing 1 Sheet (left most)and sends as attachment.

ThisWorkbook.Sheets("Submit").Copy

With ActiveWorkbook
.SendMail Recipients:="jay.fogelson@usbank.com", Subject:="Vendor Contract Info"
.Close SaveChanges:=False
End With
End Sub


I am getting a run-time error '1004' for this

[lucas]

EMail Worksheet or Workbook Using Outlook (http://vbaexpress.com/kb/getarticle.php?kb_id=97)

[Misty Shay]

Do I give it my user login & password? Ideally this should be a variable as multiple users will be accessing this spreadsheet. . .If not already logged into notes, it should prompt them to do so. Is this not an option with MAPI? What are my options?

Thanks for all your help! :help
</IMG>

[lucas]

Misty,
I'm really sorry but I don't have lotus(that's really old school isn't it?) so I really can't help you much with this...I think it operates off of your default email program which in 2007 business across the world is Outlook....I just don't know what to say or how to help you with this. Here's hoping someone will come along that can help with it. Maybe give them the option and send them to the correct script according to their choice....

[Brandtrock]

I don't have Lotus either, sorry.

[ironj32]

I have found this code for Lotus Notes, however I am getting an error at the CopySheet part. Any thoughts on what may be wrong? I want it to copy the sheet called "Submit".




Option Explicit

Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = ""
Const stSubject As String = "Vendor Management Contract Information"
Const vaMsg As Variant = "The weekly report as per agreement." & vbCrLf & "Kind regards," & vbCrLf & "Dennis"

Const vaCopyTo As Variant = "jay.fogelson@usbank.com"

Sub Send_Active_Sheet()
Dim stFileName As String
Dim vaRecipients As Variant
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String


'Copy the active sheet to a new temporarily workbook.
With ActiveWorkbook.Sheets("Submit").Copy
stFileName = .Range("A1").Value
End With
stAttachment = stPath & "\" & stFileName & ".xls"
'Save and close the temporarily workbook.
With ActiveWorkbook
.SaveAs stAttachment
.Close
End With
'Create the list of recipients.
vaRecipients = VBA.Array("jay.fogelson@usbank.com")
'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)

'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipients
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaRecipients
End With

'Delete the temporarily workbook.
Kill stAttachment

'Release objects from memory.
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
MsgBox "The e-mail has successfully been created and distributed", vbInformation
End Sub

[ironj32]

oh yeah...the error i am getting is

"Unable to get the Copy property of the Worksheet class"\


thanks!