Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: How to send document as an attachment

I know that in excel I can use this command to email the current workbook as an attachment:

Application.Dialogs(xlDialogSendMail).Show

Is there an equivalent for a Word macro?

Thanks in advance,
Damian


Post your answer or comment

comments powered by Disqus
Can anyone tell me if it is possible to set up a workbook or sheet to
send itself as an attachment to a specific email box at a specific time
of day or using a commend button setup with a macro to accomplish the
same task?

I have a worksheet designed as a form that I update and email to an external agency quite regularly. The basic premise is a button that when clicked saves a range of cells into the body of an email then sends to said agency.

I am currently using the following code:

Private Sub cmdEmail_Click()

ActiveWorkbook.Sheets("Sheet 1").Activate

   ActiveSheet.Range("B2:G8").Select
   
   ActiveWorkbook.EnvelopeVisible = True

   With ActiveSheet.MailEnvelope
      .Introduction = "Weekly Table Figures"
      .Item.To = "mail@mail.com"
      .Item.Subject = "Table No"
      .Item.Send

   End With

ActiveWorkbook.Sheets("Sheet 1").Activate

Range("A1").Select


Unload Me

End Sub
Instead of sending the cells in the body, I would like to send thenm as an attachment.

The attached workbook is an example of the cell range plus an explanation of what I need.

I have created a userform in Excel. The information from this userform is saved into a word document using a command button.

What I would like to know is can I use vba to, not only create this word document but also, to send it as an email attachment at the same time?

Just to make it a bit tougher - I'm using Groupwise as my email software.

Any help would be much appreciated.

Hi All,

I've got some codes form http://www.ozgrid.com/VBA/send-email.htm

Below Codes work fine, but just for one recipient and without any message body.
Sub SendActiveWorkbook()

    ActiveWorkbook.SendMail _
    Recipients:="1is2@gmaaal.com", _
    Subject:="Try Me " & Format(Date, "dd/mmm/yy")

End Sub
Here I would like to get the options to add more Recipients with the message body.
For Example:
To:1is2@gmaaal.com
CC:1is3@gmaaal.com;1is4@gmaaal.com;2is20@hotttmail.com
Subject: Data Updated on "System Time"
Message Body: This is a system generated mail. Please do not reply. I tried with below codes for adding more recipients, but did not get the above desired solution.
Sub Send1Sheet_ActiveWorkbook()
'
' To send any specified Excel Worksheet as an attachment to specified recipients
'Create a new Workbook Containing 1 Sheet (left most) _
 and sends as attachment.

    ThisWorkbook.Sheets(1).Copy

    With ActiveWorkbook
        Range("A1").Value = VBA.Date
         Range("A2").Value = VBA.Time
         
         .SendMail Recipients:=Array("1is2@gmaaal.com", _
                         "2is20@hotttmail.com"), _
          Subject:="Hello! " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False

    End With

End Sub
I hope you guys make it easy for me, Thanks in advance!

I have been using Outlook Express until this month, and have been sending
information from a selected range in an Excel worksheet to a number of people
every month. I don't want to send the entire (large) worksheet as an
attachment, but my "Mail Recipient" function has apparently been disabled
since installing Windows Live Mail. The only "mail" option I see remaining
is to mail the entire document as an attachment, which really isn't an option.
Since I send this information out to about 50 recipients each month, and
each recipient has different data, it's been tedious enough already. But
now, I'm looking for a way to send the data using Windows Live Mail without
having to cut and paste all afternoon.
If there is a way to efficiently do this using a Macro, that would be even
better...
Thanks,

Let's say I have a workbook open. After working on it and saving it, I need to email it as an attachment, to an email address in cell B20.What VBA code would I use, without the macro prompting me to enter the file name?And can I email an open Excel file as an attachment in the first place?If I can achieve both the above, it will be a great help to the project that I am currently working on.

I have read the code in this post with interest, and if it can be modified to fulfill my requirements, it will be great.

Thanks in advance.

Greetings,

One thing that has bothered me for a while is my inability to use times
as an axis label. Typically I have to convert everything to seconds or
something whether for use on the X- or Y-axis. I want to know - there
must be a way - how I can have a column or row of times in the format
hh:mm:ss and use those directly as label in a chart. Any help would be
greatly appreciated!

Mike

Hi,
same question posted here http://www.mrexcel.com/forum/showthread.php?t=338495

Hi,

I have about 500 sheets in excel. each sheet is named with the managers name. John, Jim, Etc, each sheet has to be emailed as an attachment to the respective manager, That is sheet John has to be email to John@gmail.com, Sheet Jim has to be emailed to Jim@yahoo.com etc.,

I have all the sheet names and their email address in one master sheet.
So some kind of vlookup has to be done between the sheet names and the email address.

When I run a macro 500 emails has to be sent according to the sheet names.
or
A userform will help me.
there should be 2 list boxes one should take all the sheet names from mastersheet, and another list box should take all the email addresses.
So I can select manually the sheet which i wanted to email as an attachment and the email address.

example:
list box one will have, SheetJohn, SheetJim, SheetMercy etc., etc(Data from mastersheet Column A)
List box two will have, Jim@gmail.com, John@yahoo.com, Mercy@hotmail.com etc( data from Mastersheet Column B)
and there should be one command button send mail.
If I select SheetJohn from list box 1 & Select
John@yahoo.com in list box 2 and then I click Send, only that sheet has to be sent as an attachment to that email address.

the subject is one line : "Outstandnig Invoice"
The body of the message is : Hi,

"Test test test test test test "

I dont know if this is possible... But Appreciate your helps.

Hi, first post here so hope I do it all right!

I have a document, in my document they are 5 sheets. In each sheet there is a hyper link that says "Mail to xxxxx" I want it so that when you click on the hyper link it sends the sheet only, not the workbook, as an attachment in an email to a defined address.

How can I do this?

I want to send Office files as an attachment with OE using File>send to> as
attachment option.

How to configure so that file goes as an attachment of OE instead of MS
Outlook or some other application.

REgards

JPJ

I want to send Office files as an attachment with OE using File>send to> as
attachment option.

How to configure so that file goes as an attachment of OE instead of MS
Outlook or some other application.

REgards

JPJ

Send to (as an attachment) in Office 2007 applications does not work when Outlook 2007 is open

I am having an issue using the Send To email as an attachment in Word, Excel, and Project when Outlook is open. I am currently running the Office 2007 SP2 product suite. The message is "General Mail Failure. Quit Microsoft Office Excel and restart mail system."
The Send To function works as expected if Outlook is closed. The document will appear in an email as an attachment. I can also right click on a file and select send to if Outlook is closed. When Outlook is open the right click on file and send does not open an email with the attachment...it doesn't do anything.
I have seen alot of people having the generic email error and their issue is resolved by checking the default programs and settings. In my case the settings are correct and its only an issue if Outlook is already opened.
Anyone else have this issue?

I'm trying to send a workbook as an attachment, but I keep getting stuck at this point in the macro.

.Attachments.Add ActiveWorkbook.MyBook2test.xls

Object doesn't support this property or method.

Exactly how does one format the attachment name etc so that it gets sent, or does anyone have actual working vba code to do this. I tried some other stuff and none of it worked.

Can I send without the Outlook Security warning.

Thanks

With a button in Excel spreadsheet I can hyperlink to create an email. I can
use FILE and SEND TO to make my Excel document as an attachment in an email.
Is there a way to do both from that first button option, force the email
return address and save the spreadsheet as an attachment? Possibly using a
Macro to control this option? My goal is for users to fill spreadsheet cells
without changing the original document and then emailing, direct to me,
their copy of my documents with their changes in it.

I am using this macro to send mail within my work book

Sub Main
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.ISOPEN) Then flag = oDB.OPEN("", "")
If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler
'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.subject = "This is the subject"
oDoc.sendto = "username@server.com"
oDoc.body = "This is test text in the body of the email"
oDoc.postdate = Date

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "c:missing.txt")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment
End Sub

I have created an excel work book that create some report file
Example c:mydataToyota Quarterly Q3-2003-customers.xls
c:mydataToyota Quarterly Q3-2003-All.xls
c:mydataToyota Quarterly Q3-2003-Errors.xls
c:mydataFord Quarterly Q3-2003-customers.xls
c:mydataFord Quarterly Q3-2003-All.xls
c:mydataFord Quarterly Q3-2003-Errors.xls

I have also a file (master_customer) that contain name of customer name and there e_mail address for example
Toyota totyota@hotmail.com
Ford Ford@hotmail.com
Mazda mazda@yahoo.com

Using above mail VB program how can I send mail only for generated customer report by using master_customer file (mail address) and a copy of all reports to myself and copy for management (only reports that have *-ALL.xls) example Toyota Quarterly Q3-2003-All.xls and Ford Quarterly Q3-2003-All.xls
My mail is mymail@yahoo.com
Manager mail is manager@hotmail.com

I do not have the option to send an opened XLS sheet as an attachment to be
sent thru Outlook express ?

Regards

Sandeep

With a button in Excel spreadsheet I can hyperlink to create an email. I can
use FILE and SEND TO to make my Excel document as an attachment in an email.
Is there a way to do both from that first button option, force the email
return address and save the spreadsheet as an attachment? Possibly using a
Macro to control this option? My goal is for users to fill spreadsheet cells
without changing the original document and then emailing, direct to me,
their copy of my documents with their changes in it.

Hi!
I'm having problem with my macro code to send mail to multiple recipient using microsoft outlook.

I have said 3 files need to send to different group of recipients (to & cc) in 3 separate mail. Therefore, I have a list of recipient address in a worksheet.

The following is my macro code, which hit an error message "runtime error 91 object variable or With block variable not set". For the 1st loop, the file can successfully send to the respective recipient. However, I hit the error message when running the 2nd loop for the 2nd file send out.
Pls help.


	VB:
	
 
Sub SendWithOutlook() 
    Dim Path            As String 
    Dim FileName        As String 
    Dim Wkb             As Workbook 
    Dim olApp As New outlook.Application, noDatabase As outlook.Namespace, noDocument As outlook.MAPIFolder 
    Dim noExplorer As outlook.Explorer, olMail As outlook.MailItem, obAttachment As Object 
    Dim outopen As Boolean 
    Dim stSubject As Variant, stAttachment As String 
    Dim toRec As Variant, ccRec As Variant 
    Dim toRecipient As outlook.Recipient, vaMsg As Variant 
    Dim ccRecipient As outlook.Recipient 
    Dim wbBook          As Workbook 
    Dim wsSheet         As Worksheet 
    Dim naAttachment    As String 
    Dim RptMM           As String 
    Dim RptDD           As String 
    Dim r               As Integer 
    Dim x               As Integer 
    Dim FileCode        As String 
    Dim y               As Integer 'start column for recipient
    Dim z               As Integer 'take how many columns for recipient
    Dim t               As Integer 
     
    Application.EnableEvents = False 
    Application.AskToUpdateLinks = False 
    Application.DisplayAlerts = False 
    Application.ScreenUpdating = False 
     
    Const wsSheetName As String = "wsSheet" 
     
    Set olMail = olApp.CreateItem(olMailItem) 
     
     
    RptMM = Format(Sheets("Act").Range("B1").Value, "MMM YY") 
    RptDD = Sheets("Act").Range("D1").Value 
     
     ' MUST CHANGE the month file
    Path = ThisWorkbook.Path 
    FileName = Dir(Path & "*.xls", vbNormal) 
    Do Until FileName = "" 
        If FileName  ThisWorkbook.Name Then 
            Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName) 
            Set wbBook = ActiveWorkbook 
            Set wsSheet = wbBook.Worksheets(Wkb.Worksheets.Count) 
             
            r = ThisWorkbook.ActiveSheet.UsedRange.Rows.Count 
            FileCode = Left(Wkb.Name, 3) 
             
            y = 4 
            z = ActiveSheet.UsedRange.Columns.Count 
             
            For x = 1 To r 
                ThisWorkbook.Sheets("Recipient").Activate 
                If Cells(x, 1).Value = FileCode Then 
                    xTo = WorksheetFunction.CountA(Range(Cells(x, y), Cells(x, z))) 
                    For t = 1 To xTo 
                        Set toRecipient = olMail.Recipients.Add(RTrim(Cells(x, t + 3).Value)) [COLOR=blue]

I have a workbook that creates a word report from excel data - and now I want to automate the emailing of the Word report...

...I'm kindof assuming that as I've already created a Word object in the code:


	VB:
	
 appWrd = CreateObject("Word.Application") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...I'd be best off using that object to send the e-mail rather than just picking up the word file as an attachment from within Excel???

Hi. I need to create a macro to send a worksheet (not workbook) as an attachment in an Outlook email.

I need to have it automatically insert the From, To, Subject, and Body info. The To field will be the info in cell A3.

Any assistance you can provide is appreciated.

Thanks
Ron

I cannot send emails from excel because the send to mail recipient as an
attachment is greyed out.

Just recently my computer will lag for an extended period of time (15-30
seconds) when I send a worksheet to a recipient as an attachment. I have
also noticed the response time is very slow when opening xls attachments from
outlook or when copying worksheet.

I am running Office 2003 Professional on an XP platform with Outlook as my
e-mail program. All microsoft updates have been done and I've re-installed
the program without any success. Any ideas as to why the program is having
such a difficult time performing such a simply task??

Thank you,
Jim

Hi all

I'm new here, so apologise if this has already been asked/answered before.

I use Office 200.

I would like to send an email message to a supervisor when a user
completes an order and pushes a button. The email needs to include
some data relating to their order, all found on one row.

The recipient is selected by the user from a dropdown list,
and this will always be on the row of the last entry.

I need to extract some data from the same row, and add the data to
the body of the message.

eg Item, Date Ordered, Lab or Plant, ETA, etc. I can do this part.
(The subject will always be the same, eg "You Have A New Order".)

At present when I use:

	VB:
	
ActiveWorkbook.SendMail Recipients:=Person, Subject:=Subject 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it sends the active workbook as an attachment.
(I used this previously, and it worked fine.)

Does anyone know how to send just an email with recipient,
subject AND body text, but WITHOUT an attachment?

Thanks in advance

Stephen B

I'd like to tell Excel to email a worksheet as an attachment and have it insert the email address in the outgoing email compose box from an address within the worksheet. (see attachment) How can I do this?


No luck finding an answer? You could always try Google.