Sending text file from vba 2

[ASAFP]

Is there a way to send a text file by mail from vba code? I found a way to send an activeworkbook, but I need to send a none-excel file.

 

[SkipVought]

ASAFP,

You don't send anything from VBA. You use VBA to send something from Excel, or Word or Access or Outlook.

So, what APPLICATION are you using to send a text file?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[ASAFP]

Skip,
Thanks for your answer.
I use outlook.
Offcoarse I dont actually send it from excel. I just need to transfer my parameters to outlook- the attached filename and the reciept. I need to send the mail at the end of one of my subs in excel.

Thanks,

Asaf

 

[fumei]

If you have send an excel file, then you know how to create an instance of outlook. The fact that you want to send a text file, as opposed to an Excel file, is basically irrelevant.

create a MailItem.
Put a Subject and Recipient into the MailItem.
Attach the file - assuming you know its folder location.
Send the MailItem.

Gerry
See my Paintings and Sculpture

 

[ASAFP]

Fumei,
Thanks for your answer.
The problem is that I used SendMail method, and it seems like only workbook can activate that method. But what can I do if I don't want to send a workbook but a text file instead?

Thanks,

Asaf

 

[Asspin]

Give this a try. You should just need to pass variables as needed. Make sure to add Outlook as a referance.

Sub SendMail(mailSubject As String, mailBody As String, mailTo As String, mailAttach as String)
  Dim ol As New Outlook.Application
  Dim olns As Outlook.NameSpace
  Dim olmail As Outlook.MailItem
  Set ol = CreateObject("Outlook.Application")
  Set olmail = ol.CreateItem(olMailItem)
    With olmail
      .To = mailTo
      .Subject = mailSubject
      .Body = mailBody
      .Attachment = mailAttach
      .Send
    End With
  Set ol = Nothing
End Sub

 

[ETID]

'You will need to add the "Microsoft Office xx.x Object Library via the tools > references menu in the vba editor....


Sub Send_Mail_with_attachmnet()

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem

On Error Resume Next

Set oOutlookApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
.To = "recipient1@mail.com"
.CC = "recipient2@mail.com"
'Set the subject
.Subject = "New subject"
'The content of the document is used as the body for the email
.Body = "The hardcoded body text goes here DUDE"
'Add the document as an attachment, you can use the .displayname property
'to set the description that's used in the message
.Attachments.Add Source:="c:\my_text_doc.txt", Type:=olByValue, _
DisplayName:="Testing Document attachment"
.Send
End With

If bStarted Then
oOutlookApp.Quit
End If

Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub

 

[fumei]

Which is exactly the list of thing I suggested to do. Although, as poiinted out you DO need the Outlook reference library.

Gerry
See my Paintings and Sculpture

 

[ETID]

Ooops correction....

'You will need to add the "Microsoft Outlook xx.x Object Library via the tools > references menu in the vba editor....

 

[tomzi]

Hello!

EDIT, first I must say that I am not a programmer, but I have some basic knowledge about VBA. I tried to use your code "Sub Send_Mail_with_attachmnet()" in Word 2003 - I putit in a macro. But when I tried to run it in VB editor I got error message in second and third row:

Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem


The message was:

"Compile error:
User-defined type not defined"


And nothing happened. What is wrong? Is MS Word so different from Excel?


Thanks

 

[fumei]

Is the Type library referenced?

Gerry
See my Paintings and Sculpture

 

[tomzi]

OK, I found out that errors in second and third line were because:
Microsoft Outlook xx.x Object Library in tools > references menu was missing.

But still nothing happens - only Outlook is nagging that one program is trying to send mail. If I confirm it with YES, new mail window doesn't open??

 

[fumei]

OK, I found out that errors in second and third line were because:
Microsoft Outlook xx.x Object Library in tools > references menu was missing.

yes, well...I wrote:

Is the Type library referenced?

; AND ETID wrote;

'You will need to add the "Microsoft Outlook xx.x Object Library via the tools > references menu in the vba editor....

so we are glad you got that one figured out yourself....

Outlook will always nag on this. Simply because Outlook leeks like a sieve via code. Check the checkbox, and press OK. This confirms that you want this to happen. When you confirm, the item will be sent. A new window will NOT open. Done properly the mailitem will simply be sent. No new window, just sent.

Gerry
See my Paintings and Sculpture

 

[tomzi]

Thanks fumei.

This morning I noticed that I have at least 12 sent mails in SENT folder. So obviously it works.

 

[PHV]

And if you want the Outlook window to open, replace .Send by .Display

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ASAFP]

I tried the code and it works.
Thanks a lot Asspin and Edit!

Asaf