Using code within Excel to change the from mail adress within outlook

[Regouin]

Hi guys,

I am completely breaking my head over this, it seems so damnright easy but i just cant get it to work.
I am using an outlook exchange server at work and all of our user names have a few mail-adresses assigned to them which they can see and from which they can send mail. Mostly a personal mail (i.e. john.doe@mycompany.nl) and a general info mail for their subpart of the company(i.e. info@mypartofthecompany.nl).
I have everything working from composing and sending the mail within excel (although i would like to know the cap on recipients or how i can find out what the cap is). No one of the recipient can see any other recipient and i implemented a reply-to adress so the person who is sending it isnt getting all the replies.
But ideally i want my macro to send the mail from the info address of our server, however .sendermailadress is a read-only object and therefor cannot be changed.
I have thought of several possible solutions but none of them seem to work...

-simply changing the from: adress, but since this is read-only or i cant find the right command i cant get it to work.
-according to an earlier suggestion by xld i tried toying with getnamespace
[vba]
Set onamespace = OL.GetNamespace("MAPI")
onamespace.Logon "info@mypartofthecompany.nl", "password" , True
[/vba]
obviously that doesnt work for me or i am doing something wrong, however i cannot check if it actually does anything, since i am not getting either an error or an approval msg
-changing the default from: address within outlook, send the email, change the default from: address back. However i did not find out what code to use for this and i am completely in the dark about where to start.

I have tried stumbling around the forums and couldnt find anything remotely related or something to help me along the way. Any input will be highly appreciated and i am running out of hope at the moment.

Thanks in Advance

Frank

[geekgirlau]

[VBA]
Sub SendEmail()
Dim objOL As Outlook.Application
Dim objML As Outlook.MailItem


On Error Resume Next
Set objOL = GetObject("", "Outlook.Application")

If Err.Number <> 0 Then
Set objOL = CreateObject("Outlook.Application")
End If

On Error GoTo ErrHandler
Set objML = objOL.CreateItem(olMailItem)

With objML
.To = "fred.flintstone@yabbadabbadoo.com"
.Subject = "Email Subject"
.ReplyRecipients = "wilma.flintstone@yabbadabbadoo.com"
.SentOnBehalfOfName = "wilma.flintstone@yabbadabbadoo.com"
.Send
End With


ExitHere:
On Error Resume Next
Set objML = Nothing
Set objOL = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical, "Unexpected Error (" & Err.Number & ")"
Resume ExitHere
End Sub
[/VBA]

[Regouin]

Thanks for the effort and i hope it really is that easy, and i like your twitch of humor in the code

thanks
frank

[geekgirlau]

As long as it works - usually I use a nonsense email address in case I send it accidently while in development mode