VBA Tutorial - Email Workbook

Dear Experts,

I have a problem & a question this tutorial on mrexcel.com: http://www.mrexcel.com/articles/macr...ily-report.php. I'm trying to utilize this in a PO system for work. Unfortunately, I keep receiving a Run-time error '9' (subscript out of range) on this specific line:

Code:

Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)

The code is strictly copy/pasted into my VBA editor, except changing the names inbetween each of the quotes ("") for specific sheet/cell names. I'm not a programmer, but I've read through the code and logically can't deduce why it shouldn't work for me.

My other related question is that I would like to have the new worksheet sent to the end of the active workbook, so would substituting "Sheets(Sheets.Count)" at the end of the above line of code work to send it to the end of the workbook?

Thanks for your help ahead of time.

can I progmatically (macro in Excel 2007) open this email message (saved)
file://\\Ac35dfpspsD2p\s3_data$\MEPSFLR\FLR CDR\5d Morning Report.msg
and attach this file to the email msg
file://\\Ac35dfpspsD2p\s3_data$\MEPSFLR\FLR CDR\FloorReport.xls
automatically so, all I have to do is click send...............???

All,

I am working on an Excel Macro to email the related workbook and would like to allow the user the option of attaching an additional file (probably a PDF) to the email before sending it. Please see the code below and attached excel spreadsheet.

Code:

Sub Send_Worksheet_as_Email()
    
    Dim email As String
    Dim attachment As String
    
    email = InputBox("Please enter the Email address you want to send to:", "Enter Email")
    'Change email address as required
    
    MsgBox "Would you like to add an attachment?", vbQuestion + vbYesNo
    
    If vbQuestion = vbYes Then

     
         
ChooseDirectory:
    
    ActiveWorkbook.SendMail Recipients:="" & email
        
        With ActiveWorkbook.MailEnvelope.Attachments.Add
                
                attachment = Application.GetOpenFilename
            
            If attachment Like "Enter The Attachment Name Here." Then
                
                MsgBox "Please enter your attachment's name here."
                
                GoTo ChooseDirectory
           
          
            End If
        
        End With
    
    Else
       
    End If
    
    MsgBox "You have sent the completed REQUEST FOR INFORMATION to the following address: " & email
   
End Sub

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.

Goood Morning

I have a button on a user form which I want to use to email the entire workbook to any desired email address. Is there a way which when this button is clicked, the user will have to enter the desired email address and click another button (send).

hey presto..the workbook should send to the email address which they entered.

Is there a way of doing this or is it going to be MEGA complicated?????

Hi all,

I have a massive workbook 100+ sheets

I have on a worksheet called "Email List" 4 collumns
Rows 2 - any length

Collumn A is a list
Collumn B Has the names of some worksheets
Collumn C has the names of some worksheets
Column D Is a list of emails

I need a macro to create a new workbook and COPY the sheet in column B and C to this new workbook.

Then convert this workbook to a PDF and save the file as Column A + todays date
To a folder

Then email the Pdf to the address in Column D

Then move to the next row in the sheet "Email List"
Stop when last row reached

Simples?!?

Help with the whole thing or just a section is welcome.

Much thanks and awe,

Greg

can anyone help please with the following problem

i want to email a workbook, which i can do with sendmail, but the problem is that i would like to add a message to the email?????

also tried using the routeslip method but as my address book has been deleted and cant be restored (moved email account from one pc to another...works email) and this wont work as it doesnt recognise the email address and come up with 'Unknown'

would appreciate any help quickly

many thanks

Hi all,

I want to email my workbook by a button that is in sheet 1 ,using VBA, to an emailadress than is in cell A2 in sheet 2.

Tried ceveral things, but can't get it to work.

(if possible using outlook 2003 and windows live mail).

Thanks,

I would like to learn VB, can any one guide me from where i will get free online study material

Hi all,
I am relatively new to excel and keen to enhance my knowledge of it. I was wondering if there any tutorials or walk through 'question and answer' style pages or programs that anyone knows of which could help me. Ideally what i'd be looking for would be a number of problems that need to be solved using certain formulae. The aim of which would be to be introduced to new formulae and new ways of solving problems.

If anyone knows of anything that could help me out, it'd be greatly appreciated.

cheers