Solved: Emailing a Word document without using mail client

[mugcy]

HI,

I need setup a word Form with a send button, when it's pressed the Form gets emailed without going through a mail client.

Is this possible? If so how can I do it?

Please help: pray2:

[lucas]

gets emailed without going through a mail client.





I don't understand. You can send using Outlook but outlook has to be set up to send mail.

[fumei]

How on earth can you mail something without using a mail client? That is what a mail client does...mail things. Things do not get mailed magically. If it goes out as SMTP (Simple Mail Transfer Protocol) it MUST use...a mail client.

For sure, there are applications that hide this fact, but it is a fact nonetheless.

What exactly is your situation?

[mugcy]

situation is as follows
Users do not have a mail client configured on their computers. They use web based Outlook to send/ receive emails. On there desktops there are some word forms (to apply for leave). So when they press the send button, those forms have to be mailed.

since the mail client is not configured how can this be done?

[fumei]

Darn darn darn.

Sorry, I simply do not know. I am not sure you can do that with VBA through Word. AFAIK, Word can only use objects acting through "normal" exposed channels in the Object Model. Which means an instance of Outlook. However, there is no such instance in this case.

Interesting. I shall look, and keep an eye on this thread in case someone else has an idea. As it stands, I suspect this is not possible.

It may though. Regardless, something is acting as a mail client. Like I said, some applications hide it - like this case - but a mail client IS being used. It may not be exposed to VBA though.

[lucas]

situation is as follows
They use web based Outlook to send/ receive emails.

So, is it outlook or just an outlook lookalike?

There is no such thing as web based outlook except outlook which uses the web.

[geekgirlau]

Are you referring to Outlook Express?

Even if they are using a web-based client, it is still connecting to a mail client of some description. There are lots of examples available for using SMTP to send mail with specifying the application to use in sending the mail, but you still need to find out the mail server to use.

[mugcy]

The users are using exchange server accounts which they access through the web.

[geekgirlau]

Here is a very simple macro to send email via SMTP:


Sub SendEmail()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~
' Purpose: Email sent via SMTP to avoid the annoying Microsoft warning message
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~
Dim objCDOConfig As Object
Dim objCDOMessage As Object
Const cdoschema = "http://schemas.microsoft.com/cdo/configuration/"
Const cdoSendUsingPort = 2
Const cdoBasic = 1
Const cstrServer = "mail.blahblah.au"
Const cstrFrom = "annie@mymail.com.au (annie@mymail.com.au)"

On Error GoTo ErrHandler
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(cdoschema & "sendusing") = cdoSendUsingPort
.Item(cdoschema & "smtpserver") = cstrServer
.Item(cdoschema & "sendusername") = cstrFrom
.Update
End With
Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = cstrFrom
.Sender = cstrFrom
.To = "Set email recipients here"
.Subject = "Set email subject here"
.HTMLBody = "Set message (with html tags) here"
.Send
End With

ExitHere:
On Error Resume Next
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
Exit Sub
ErrHandler:
MsgBox "(SendEmail: " & Err.Number & ")" & vbCrLf & Err.Description, vbCritical, _
"Unexpected Error"
Resume ExitHere
End Sub


You will need to find out the name of the exchange server, and the email address that the message will be coming from, which needs to be a valid email address. Both of these lines are highlighted above.

[fumei]

Interesting. Please let us know if this works.

[mugcy]

Thank you very much geekgirlau.

I tested this and it definitely lets me send email without going through an email client.

It sends the ".HTML Body" part in the email.

[mugcy]

Is there any way to send an attachment using the above code?

[fumei]

Use .AddAttachment.

With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = cstrFrom
.Sender = cstrFrom
.To = "Set email recipients here"
.Subject = "Set email subject here"
.HTMLBody = "Set message (with html tags) here"
.AddAttachment "c:\temp\readme.txt"
.Send
End With


The file it points to MUST be fully qualified.

[geekgirlau]

Interesting. Please let us know if this works.

What do you mean, IF? :tongue:

You have a choice of using HTMLBody or Body to set the body text for the email. I tend to use HTMLBody so I can use HTML tags to format the text.

With the attachment, make sure you test to see that the file exists first. You could do something like this:


If Dir("c:\temp\readme.txt", vbNormal) = "" Then
MsgBox "The attachment does not exist", vbExclamation, "File Not Found"
Else
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = cstrFrom
.Sender = cstrFrom
.To = "Set email recipients here"
.Subject = "Set email subject here"
.HTMLBody = "Set message (with html tags) here"
.AddAttachment "c:\temp\readme.txt"
.Send
End With
End If

[fumei]

By "if" I meant if it works well enough for the purpose. I cannot get CDO to work here, for whatever reason.

[mugcy]

I tried to send an attachment with the following code. But when it comes to the .AddAttachment It gives the message "The process cannot accessthe file because it is being used by another process. I tried setting Doc to nothing but didn't help

Sub SendEmail()

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~

' Purpose: Email sent via SMTP to avoid the annoying Microsoft warning message

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~

Dim objCDOConfig As Object

Dim objCDOMessage As Object

Const cdoschema = "http://schemas.microsoft.com/cdo/configuration/"

Const cdoSendUsingPort = 2

Const cdoBasic = 1

Const cstrServer = "mail.abc.com.au"

Const cstrFrom = "blah@tblah.com.au"

Dim Doc As Document

Set Doc = ActiveDocument

Doc.SaveAs ("c:\temp\Document Form1.doc")



On Error GoTo ErrHandler

Set objCDOConfig = CreateObject("CDO.Configuration")

With objCDOConfig.Fields

.Item(cdoschema & "sendusing") = cdoSendUsingPort

.Item(cdoschema & "smtpserver") = cstrServer

.Item(cdoschema & "sendusername") = cstrFrom

.Update

End With

Set objCDOMessage = CreateObject("CDO.Message")
'Set Doc = Nothing

If Dir("c:\temp\c:\temp\Document Form1.doc", vbNormal) = "" Then
MsgBox "The attachment does not exist", vbExclamation, "File Not Found"
Else
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = cstrFrom
.Sender = cstrFrom
.To = "someone@yahoo.com.au"
.Subject = "Document Form1.doc"
.HTMLBody = "Set message (with html tags) here"
.AddAttachment "c:\temp\Document Form1.doc"
.Send
End With
End If




ExitHere:

On Error Resume Next

Set objCDOMessage = Nothing

Set objCDOConfig = Nothing

Set Doc = Nothing

Exit Sub

ErrHandler:

MsgBox "(SendEmail: " & Err.Number & ")" & vbCrLf & Err.Description, vbCritical, "Unexpected Error"

Resume ExitHere

End Sub



It works if I don't save the document but I have to save the changes.
How can I get it fixed?

[fumei]

Close the document first. That is, do your SaveAs, then a .Close. It appears the fact it is still open makes it "used by another process".

Of course this becomes an issue if the code your are executing is in THAT document.

If it is, you may have to take it out and put it in a global template.

[lucas]

Ann, what if your server requires authentication?

[mugcy]

So what is the difference with following code and the above when it comes to saving a document and emailing it as an attachment? other than the fact that latter uses an Outlook object

Private Sub Email_Form_Click_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Dim Heading As String

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.SaveAs ("c:\temp\Document Form1.doc")

With EmailItem
.subject = "Document Form1"
.To = "blah@blah.com.au"
.Importance = olImportanceHIgh 'Or olImprotanceNormal Or olImprotanceLow
.Attachments.Add Doc.FullName
.Display
' .Send
End With

Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub


This code lets me email the document without closing it.

[fumei]

Well for starters Outlook and Word are both Microsoft products and are - sort of - designed to work together. Thus, Outlook can handle a Word document object with no clashes with Word. I suspect what it does is make an internal copy of the file, thus by-passing the handles for it.

Apparently CDO does not. It sees something (Word) is "using" it.

[kusaan]

Thanks MAn
The code really Work
Thanks for saving my time

[geekgirlau]

Ann, what if your server requires authentication?

Generally the email server will check whether you have permissions to send on behalf of the email address that you have specified, so you need to ensure that your error checking will give you a definitive answer as to why an email failed to be sent. Have used this successfully with Outlook and Lotus Notes.

Mugcy, the reason I tend to use this rather than using Outlook is because of the highly irritating security messages. Try running a procedure that queues up 50 emails and you'll have to spend a lot of time telling Outlook that it IS okay to send the message!

And Lucas, it's AnnE, or I'm afraid I'm going to have to ignore you in future! :devil2:

[fumei]

Fussy fussy fussy

[fumei]

BUMP

Any links you can give me to try and get this working for me. I get a critical error "Transport failed to connect to the server."

This does not really tell me anything...except that the transport failed to connect to the server. It steps through fine until it hits the .Send

[Tinbendr]

Transport failed to connect to the server
This link (http://www.systemwebmail.com/faq/4.3.9.aspx) seems to on forever. I bet you could find something there. (Unless is an anti-virus blocking it.)

[fumei]

" (Unless is an anti-virus blocking it.)"

Strange...why did you say/mention that, because....yes, our firewall returned a:

FORBIDDEN by rating check.

and I am on the "going-to-a-bad-place" list, again.

What on earth is there?

[geekgirlau]

Yes I've had similar issues with some clients, even when sending only internal emails! Don't have a definitive solution as yet.

[Digby]

I've used VBA in the past to email as an attachment by first saving the file to the name I want, then re-saving to another 'random' file name.

However, when upgrading from Word 2003 to Word 2010 it stopped working. Both the file we want to attach AND the temporary random name are locked and unable to send??

So I adjusted the script to simply save the document once the use the FileSystemObject to copy file file to another version. Yes, strangely enough even though the file is still locked by Word, the FSO will copy to a new name.

Here's my code:


Private Sub SUBMIT_Click()
'*** SUBMISSION SCRIPT FOR AIRPORT HOTEL HALIFAX
'*** DESIGNED BY DIGBY CUNNINGHAM
'*** (C) 2007 Vi-Net Computer Solutions
'*** CODE LAST MODIFIED June 5th 2011

'*** NOTE: PLEASE ENSURE FIELD ON THE FORM WITH THE DATA HAS THE NAME "DATE" FOR THE FOLLOWING
'*** CODE TO WORK PROPERLY. ALSO MAKE SURE THE DATE FIELD FORMAT IS "DDDD, MMMM D, YYYY"


'*** DECLARATIONS ***
Dim FSO As Object

strFile = "AM Shift Report"
strMailRecipient = "xyz@abc.xom"
strMailFrom = "xyz@abc.com"
strMailServer = "smtp.yourmailserver.org"
strFileTemp = Str(Int(Rnd() * 1234567))
'**********CHANGE ONLY THE VALUES ABOVE THIS LINE*********
'*********************************************************

'*** CHECK TO MAKE SURE WE HAVE THE MANDITORY DATE ENTERED!
If ActiveDocument.FormFields("Date").Result = "" Then
NUL = MsgBox("You must enter a valid date before submitting", vbOKOnly, "Submit Error")
ActiveDocument.FormFields("DATE").Select
Exit Sub
End If

'*** Ensure they really want to do this
strAnswer = MsgBox("Are you sure you wish to submit this report?", vbYesNo, "Submit?")

If strAnswer = vbYes Then
'*** CONVERT DATE ON WORD FORM TO CORRECT FORMAT
strDate = ActiveDocument.FormFields("Date").Result
intWhere = InStr(strDate, ",")
strDate = Right(strDate, Len(strDate) - intWhere)
strDate = Format(strDate, "yyyy-mmmm-dd")

'*** SAVE THE DOCUMENT TEMPORARILY
strPath = Environ("TEMP")
ChangeFileOpenDirectory strPath

strFileName = strFile & " - " & strDate
strFileNameTemp = strFileName & "A"

ActiveDocument.SaveAs FileName:=strFileNameTemp, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

'*** COPY FILE TO AN UNLOCKED OBJECT
Set FSO = CreateObject("scripting.filesystemobject")
strFromFile = strPath & "\" & strFileNameTemp & ".doc"
strToFile = strPath & "\" & strFileName & ".doc"
FSO.CopyFile Source:=strFromFile, Destination:=strToFile

strFileName = strPath & "\" & strFileName & ".doc"

'*** SEND AN EMAIL WITH DOCUMENT AS ATTACHMENT
Set objEmail = CreateObject("CDO.Message")
objEmail.From = strMailFrom
objEmail.To = strMailRecipient
objEmail.Subject = strFile & " - " & strDate
objEmail.Textbody = strFile & " for the date of " & strDate
objEmail.AddAttachment strFileName

'*** NOTE::: HAD TO PUT "." (dots) IN THE FOLLOWING THREE DIRECTIVES TO POST IN THIS FORUM :) REMOTE THE DOTS IN THE .H.T.T.P.:././ FOR IT TO WORK CORRECTLY
objEmail.Configuration.Fields.Item(".h.t.t.p.:././schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item(".h.t.t.p.:././schemas.microsoft.com/cdo/configuration/smtpserver") = strMailServer
objEmail.Configuration.Fields.Item(".h.t.t.p.:././schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send


Application.Quit

Else
Exit Sub
End If

End Sub

[Kenny22]

Try an external web based outlook approach?