Sending emails with attachments.

[Badvoc]

Hi
Im using “sendmessage” in a module to send an e-mail with an attachment.
The code Im using is copied from HERE
The code in full

[FONT=Courier New][FONT=Courier New]Sub sbSendMessage(Optional AttachmentPath)[/FONT]
[FONT=Courier New] Dim objOutlook As Outlook.Application[/FONT]
[FONT=Courier New] Dim objOutlookMsg As Outlook.MailItem[/FONT]
[FONT=Courier New] Dim objOutlookRecip As Outlook.Recipient[/FONT]
[FONT=Courier New] Dim objOutlookAttach As Outlook.Attachment[/FONT]
 
[FONT=Courier New] On Error GoTo ErrorMsgs[/FONT]
 
[FONT=Courier New] ' Create the Outlook session.[/FONT]
[FONT=Courier New] Set objOutlook = CreateObject("Outlook.Application")[/FONT]
[FONT=Courier New] ' Create the message.[/FONT]
[FONT=Courier New] Set objOutlookMsg = objOutlook.CreateItem(olMailItem)[/FONT]
[FONT=Courier New] With objOutlookMsg[/FONT]
[FONT=Courier New]    ' Add the To recipient(s) to the message. Substitute[/FONT]
[FONT=Courier New]    ' your names here.[/FONT]
[FONT=Courier New]   Set objOutlookRecip = .Recipients.Add("Nancy Davolio")[/FONT]
[FONT=Courier New]    objOutlookRecip.Type = olTo[/FONT]
[FONT=Courier New]    ' Add the CC recipient(s) to the message.[/FONT]
[FONT=Courier New]    Set objOutlookRecip = .Recipients.Add("Andrew Fuller")[/FONT]
[FONT=Courier New]    objOutlookRecip.Type = olCC[/FONT]
[FONT=Courier New]    ' Set the Subject, Body, and Importance of the message.[/FONT]
[FONT=Courier New]    .Subject = "This is an Automation test with Microsoft Outlook"[/FONT]
[FONT=Courier New]    .Body = "Last test." & vbCrLf & vbCrLf[/FONT]
[FONT=Courier New]    .Importance = olImportanceHigh  'High importance[/FONT]
[FONT=Courier New]    ' Add attachments to the message.[/FONT]
[FONT=Courier New]    If Not IsMissing(AttachmentPath) Then[/FONT]
[FONT=Courier New]       Set objOutlookAttach = .Attachments.Add(AttachmentPath)[/FONT]
[FONT=Courier New]    End If[/FONT]
[FONT=Courier New]    ' Resolve each Recipient's name.[/FONT]
[FONT=Courier New]    For Each objOutlookRecip In .Recipients[/FONT]
[FONT=Courier New]       If Not objOutlookRecip.Resolve Then[/FONT]
[FONT=Courier New]          objOutlookMsg.Display[/FONT]
[FONT=Courier New]    End If[/FONT]
[FONT=Courier New]    Next[/FONT]
[FONT=Courier New]    .Send[/FONT]
[FONT=Courier New] End With[/FONT]
[FONT=Courier New] Set objOutlookMsg = Nothing[/FONT]
[FONT=Courier New] Set objOutlook = Nothing[/FONT]
[FONT=Courier New] Set objOutlookRecip = Nothing[/FONT]
[FONT=Courier New] Set objOutlookAttach = Nothing[/FONT]
[FONT=Courier New]ErrorMsgs:[/FONT]
[FONT=Courier New] If Err.Number = "287" Then[/FONT]
[FONT=Courier New]    MsgBox "You clicked No to the Outlook security warning. " & _[/FONT]
[FONT=Courier New]    "Rerun the procedure and click Yes to access e-mail" & _[/FONT]
[FONT=Courier New]    "addresses to send your message. For more information" & _[/FONT]
[FONT=Courier New]    "see the document at http://www.microsoft.com/office" & _[/FONT]
[FONT=Courier New]    "/previous/outlook/downloads/security.asp. " "[/FONT]
[FONT=Courier New] Else[/FONT]
[FONT=Courier New]    Msgbox Err.Number, Err.Description[/FONT]
[FONT=Courier New] End If[/FONT]
[FONT=Courier New]End Sub[/FONT]
[/FONT]

I have 2 problems that need solving.
Firstly.
Where the code calls for the recipients name ie
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
I want it to look at a combo box on a form that has the full email address in it.
How would that be written? I'v tried a number of diferrent ways, all failed

Also for the AttachmentPath
I have a button on the form, in the on click event to run the code in the module it has
=sbSendMessage()This will send a email without an attachment, if I put the path in the () ie
=sbSendMessage(C:\Documents and Settings\Desktop\This is an Automation test with Microsoft Outlook.msg) It will attach the file and send, but I need the path to be got from a text box on the form as this has a browse function to add a path to it, (can't have users editing the properties to add emails.)

Thanks in advance
B

 

[Badvoc]

I managed to fix the attachmentpath problem just by adding "as string" after "Optional AttachmentPath"

but Im still stuck with how to get it to read the addres from a form and not outlook.
Help...

 

[Badvoc]

nobody know then?

 

[stopher]

Assuming you are calling this routine from the same form that contains the combo then:

Set objOutlookRecip = .Recipients.Add(me.myComboName)

This assumes the combo is in the main form and not in a sub form.

hth
Chris

 

[Badvoc]

Assuming you are calling this routine from the same form that contains the combo then:

Set objOutlookRecip = .Recipients.Add(me.myComboName)

This assumes the combo is in the main form and not in a sub form.

hth
Chris

Im using a public module.
Iv tried this

Set objOutlookRecip = .Recipients.Add([Forms]![form12]![Combo11])

looks like the emial will send to start with but dosn't actually send it.
could it be because combo11 has a column count of 3 and only the first one showing which contains the recipients name.
how would change the above code just to read the first column of combo11?

PS thanks for answering

 

[mattkorguk]

Set objOutlookRecip = .Recipients.Add([Forms]![form12]![Combo11].column(0))

Might do the job.

 

[Badvoc]

Set objOutlookRecip = .Recipients.Add([Forms]![form12]![Combo11].column(0))

Might do the job.

Thanks