Send file direct on email with attachment

[Closed]
Report
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010
-
Posts
13
Registration date
Wednesday November 3, 2010
Status
Member
Last seen
November 15, 2010
-
Hello,

I have created a survey in excel 2003.
I want an email address to hyperlink to an email with the persons email address and attaching the survey.
I have been looking at VBA codes but none are working.

Email address is in Cell B49
Please help
Thanks

11 replies

Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Rizvisa1, You normally help me, can you take a look and see what you think, could do ith the answer asap as Im being pushed. Thanks
Posts
13
Registration date
Wednesday November 3, 2010
Status
Member
Last seen
November 15, 2010
1
Is the file (survey) you are attaching currently active? Did you want a button to loop through all your emails on column B or did you want to send the email when you click on cell B49. A sample file would help it much easier.
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Hi, I would like the person filling out the survey to click on the email hyperlink and the excel file will attach to the email. The email is to be pre addressed and also have a subject heading "RMA Survey". where can i attach the survey to show you?
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Just to let you know that I cannot upload the file to any of these sites as my company has blocked access.. https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Hi,
The survey has been uploaded. please see below link
https://authentification.site/files/25130059/Survey_2.xls

Please can you look at this today asap as I would like to send it out by tomorrow.
Thanks for your help on this,
Posts
13
Registration date
Wednesday November 3, 2010
Status
Member
Last seen
November 15, 2010
1
Paste this on your RMA survey sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$65" Then
    Call MailOut  ' in this case if A1 is clicked
End If
End Sub



On your module, paste this:

Option Explicit

Sub MailOut()
'Working in 2000-2010
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2010
        FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ThisWorkbook.Worksheets
        
            sh.Copy
            Set wb = ActiveWorkbook

            TempFileName = sh.Name & " " & Format(Now, "mm-dd-yyyy")

            Set OutMail = OutApp.CreateItem(0)

            With wb
                .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

                On Error Resume Next
                With OutMail
                    .To = "FDan@anovo.com"
                    .CC = ""
                    .BCC = ""
                    .Subject = "RMA Survey"
                    .Body = "RMA Survey attached."
                    .Attachments.Add wb.FullName
                    'You can add other files also like this
                    '.Attachments.Add ("C:\test.txt")
                    .Display 'Make sure to comment this if you are using .Send below
                    '.Send 'uncomment .send if you want to send directly without pressing Send button
                End With
                On Error GoTo 0

                .Close SaveChanges:=False
            End With
            Set OutMail = Nothing

            Kill TempFilePath & TempFileName & FileExtStr

    Next sh

    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub



Let me know if this works for you.
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Hi, this only works when you press run macro. However when you click on the email address in cell c65 it does not attach the file....
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

ok tried it again and if you click on it first time it doesnt attach the file. However you need to press down CTRL and then let go anf then click again... this may be too confusing for customers...
please help so that all you need to do is click once. thanks.
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

I have got someone else externally to try it using excel 2007 and the link does not attach the file.
Posts
13
Registration date
Friday June 11, 2010
Status
Member
Last seen
December 6, 2010

Hi, I fiddled with the hyperlink and now it works.
The external persons one now works also as they were not enabling the macro.
Thanks for your help on this I really appriciate it.
Now on to the second bit of this project.... I would like to convert the results I receive from this survey onto a table, and a pivot table. this has to show me how many surveys received plus all the answers from the questions so i can analyse the results.
I would like this on just one pivot table / table as at the moment we are having to manually fill in the data and its taking too long. how would this be possible? Thanks
Posts
13
Registration date
Wednesday November 3, 2010
Status
Member
Last seen
November 15, 2010
1
Sorry, thats too much work. You can try to record a macro to get it to open your files and then do a pivot table and see the results on the macro. Otherwise, let me apply for your work that you are doing.. I'm in need of a full time job with benefits.