Help with 'easy' project.

[Massey1337]

Okay my dad has set me a project, apparently it's no more than 6 lines, but I can't get my head around it.
The idea is on an excel worksheet is to enter the name of the file and the data (user does this manually) then clicking the command button will refresh the worksheet, combine the contents of these 2 cells and save the file and e-mail the file to the same e-mail address every time.

My current code is:

Sub Button1_Click()
Dim fname As String
Dim MyDate
MyDate = Date
fname = Worksheets("Sheet1").Cells(3, 2)
fname = fname + MyDate
ActiveWorkbook.SaveAs fname

End Sub

I really need help on this, it's sending me round the bend, thanks.

 

[VoG]

Try

Sub Button1_Click()
Dim fname As String
fname = Worksheets("Sheet1").Cells(3, 2)
fname = fname & " " & Format(Date, "dd-mm-yy") & ".xls"
ActiveWorkbook.SaveAs fname
End Sub

For code to e-mail look here http://www.rondebruin.nl/sendmail.htm

 

[Massey1337]

Thanks for your help.
It currently saves the file as just the data, preferably I'd like the name that the user manually enters in front of the date.
e.g. Cell (3, 2) has Elliot Massey, file saves as 'Elliot Massey 21/03/2009.xls'

 

[VoG]

preferably I'd like the name that the user manually enters in front of the date.
e.g. Cell (3, 2) has Elliot Massey, file saves as 'Elliot Massey 21/03/2009.xls'

That's exactly what it does. Make sure that you enter something in B3 before running the macro.

 

[Massey1337]

Ah thank you got it now, had it in C2.
I've also tried googling refreshing (recalculating) the worksheet, but again, I'm having no luck with my code.
I've looked through the e-mail function and I can't get it to work
I'm getting the error: Microsoft Office Outlook No profiles have been created. To create a new profile, use the mail icon in the Control Panel.
And: Run-time error '1004': Method 'SendMail' of object ' _Workbook' failed
I don't want to have to create a new profile to complete this.
This is my current code now:

Sub Button1_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim fname As String
fname = Worksheets("Sheet1").Cells(2, 3)
fname = fname & " " & Format(Date, "dd-mm-yy") & ".xls"
ActiveWorkbook.SaveAs fname
wb.SendMail "elliot_massey@hotmail.co.uk", _
"Attachment sent."
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If GetXLCol(Target.Column) = "G" Then
Calculate
End If
End Sub

 

[VoG]

I'm not sure what

Private Sub Worksheet_Change(ByVal Target As Range)
If GetXLCol(Target.Column) = "G" Then
Calculate
End If
End Sub

has to do with this but it belongs in the sheet's code module. Right click the sheet's tab, select View Code and place the code in there.

The e-mailing code works fine for me as you'll see if you check your Inbox - I sent it as an .xslm file as I'm using Excel 2007.

 

[Massey1337]

Changed it to what you said, but I still get the no profiles have been created blah blah and Runtime error 1004 Application-defined or object-defined error

 

[Sanjeev1976]

Click on Microsoft Outlook from the programs and create a profile with your email id. Since if you send an email to any email id, excel will select the default profile set in Microsoft Outlook and send an email.

 

[Massey1337]

How do you know the server information like POP3 and that?

 

[Sanjeev1976]

Search the internet using Google or some other search engine for your respective email account