Alternatives to Sendobject

tmort

Registered User.
Local time
Today, 20:25
Joined
Oct 11, 2002
Messages
92
I'd like to automate sending some query answer tables as tab delimited text files attaced to an email.

I'm finding that sendobject can't send in this format.

Are there any alternatives?

Thanks
 
Hi,
Yo can do it in parts (maybe there's a better way, but I currently use that one, I think it’s more customizable):

1) Create the file or files to be sent "as you want"
2) Send the file by Outlook
3) Delete the file

The first point is easy, you can use an “OutputTo” sentence or the "CreateObject("Scripting.FileSystemObject").CreateTextFile" one to made a customized TXT file. This is the code for the second and third points:

Code:
Dim  OutlookMessage As Object

Set OutlookMessage = CreateObject("Outlook.application").createitem(0)

'[COLOR=SeaGreen]Repeat the following line for every file you want to attach to the Message[/COLOR]
OutlookMessage.attachments.Add "String with the fullpath of the File stored"
OutlookMessage.subject = "Subject if needed"[COLOR=SeaGreen] ' Optional line[/COLOR]
OutlookMessage.body = "Message if needed"  [COLOR=SeaGreen]' Optional line[/COLOR]
OutlookMessage.recipients.add = "email address"  '[COLOR=SeaGreen]Recipient or list of recipients of the message[/COLOR]
OutlookMessage.display [COLOR=SeaGreen]' Optional line, shows the message without
'sending it (only if you want to check it manually). If not, use instead:[/COLOR]
OutlookMessage.send '[COLOR=SeaGreen]That will send the message automatically[/COLOR]
Set OutlookMessage =nothing

Kill   "String with the fullpath of the File stored" [COLOR=SeaGreen] 'This is the third point [/COLOR]

Uh.. maybe a little bit complicated, but as I said this method has the advantage it's very easy to customize. Hoping it suits you...
 
Thanks, that looks like it should work. The application will be distributed to others. They won't all necessarily have outlook as their default email, but, they will have Office so they will have Outlook.
 
I don have a question though. I'd like to send the message to a cc recipient as well as a to recipient.

I've gotton some code on how to specify a cc recipient type but haven't quite figured out how to fit it into the code you provided.

What I have is:

Dim outlookapp As Application
Dim outlookmessage As Object


Dim Item As Outlook.MailItem
Dim objMe As Object


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmessage = outlookapp.CreateItem(olMailItem)



Set objMe = Item.Recipients.Add("ccaddress@somewhere.com")
objMe.Type = olBCC
objMe.Resolve
Set objMe = Nothing




With outlookmessage
.Recipients.Add "john@somewhere.com"
Recipients.Add "jane@ci.grand-rapids.mi.us"
.Subject = "test"
.Body = "message?"
.Importance = olImportanceHigh
'.Attachments.Add (App.Path + "\FILENAME.TXT")
.Send
End With
 
Hi again,
For a copy to somebody, according to the code I wrote the correct codeline would be:

OutlookMessage.recipients.Add (email address).Type = 2

For a hidden copy the code would be the same changing type "2" for type "3". A "normal" recipient would be a "Type 1", but it's not showed in my code because it's the default option.

The sample you got is in Outlook VBA, and adds a "hidden copy" recipient. As a comment, I think that the Outlook or Excel VBA constants doesn't work directly in Access VBA, so if you got code in these applications you have to change the "name" of the constant for its value before using it in an Access VBA module. Or better explained with an example:
In Access VBA, to maximize the Application window you can use:
Docmd.runcommand acCmdAppMaximize
"acCmdAppMaximize" is an Access constant, its value is "10" so this code will do the same:
Docmd.runcommand 10
If you put the "type of recipient to add" = OLBCC (in the code you got), Access VBA won't understand it, because it's an Outlook constant (hey, note that Access constants always start by "ac", Outlook ones by "ol"...). But it will work if you change the constant name for its VBA value (in this case, OLBCC=3=Hidden copy recipient). It's the same for the object created, in the Outlook code is "olMailItem", in my example was just "0" what means the same.
If you want to know the "real value" of a constant, just ask for it in the immediate window of the VBA editor of the application, like this:
?acCmdCopy
(answer: "190")

Buffffff... sorry for being so boring, I'm in the point of sleeping myself. Just a final comment: depending on the security level of the user, doing "automatic" emails can generate continuous warnings of the application (something like "hey!!! someone is trying to send an email using your PC!!!)... just thing of it.
 
Oops

I may have spoke to soon.

I tried it just in outlook and if worked fine. Now I am trying it along with code to create the file to send in Access and it doesn't work.

The code I am using is:

Dim outlookapp As Application
Dim outlookmessage As Object
Dim Item As Outlook.MailItem


DoCmd.TransferText acExportMerge, , "process export qry", "Process Control Export.txt", False


'Dim objMe As Object


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmessage = outlookapp.CreateItem(olMailItem)


With outlookmessage
.Recipients.Add "to@email.com"
.Recipients.Add("cc@email.com").Type = 2
.Subject = "Test"
.Body = "Message"
.Importance = olImportanceHigh
'.Attachments.Add (App.Path + "\ Process Control Export.txt")
.Send
End With

Kill "Process Control Export.txt"


And I get a Method or data member not found (Error 461)
at the line:

Set outlookmessage = outlookapp.CreateItem(olMailItem)

I have references set to Outlook and have the statement Dim Item As Outlook.MailItem. I'm not sure what the error is about.

Thanks for any help
 
tmort,

I know this is years later but this is to help other users out as I had problems finding the answer. Here's the code I used that worked:
Code:
Function SendOutlookEmail(ByRef olApp As Object, ByRef subject As String, ByRef toRecip() As String, ByRef ccRecip() As String, ByRef bodyText As String) As Boolean
On Error GoTo Err_Handler
    Dim objMail As Object
    Dim recip As String
    Dim i As Integer, numRecips As Integer
 
    Set objMail = olApp.createitem(0) 'OlItemType.olMailItem = 0
    With objMail
        numRecips = UBound(toRecip)
        For i = 1 To numRecips
            If LenB(toRecip(i)) > 0 Then
                .Recipients.Add toRecip(i)  'OlMailRecipientType.olTo = 1, default though so not required
            End If
        Next i
        numRecips = UBound(ccRecip)
        For i = 1 To numRecips
            If LenB(ccRecip(i)) > 0 Then
                .Recipients.Add(ccRecip(i)).Type = 2    'OlMailRecipientType.olCC = 2
            End If
        Next i
        .BodyFormat = 1 'OlBodyFormat.olFormatPlain = 1
        .subject = subject
        .Body = bodyText
        .send
    End With
    Set objMail = Nothing
    SendOutlookEmail = True
Exit_Handler:
    Exit Function
 
Err_Handler:
    If Err.Number < 0 Then  'Normally some large negative number if .send fails
        Call MsgBox("A contact is formatted incorrectly. Please correct the email address. Saving email to drafts.", vbExclamation, "Invalid Email Address")
        objMail.Save
        SendOutlookEmail = True
    Else
        MsgBox "Error sending email via Outlook."
        SendOutlookEmail = False
    End If
    Resume Exit_Handler
End Function
 

Users who are viewing this thread

Back
Top Bottom