Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 26th, 2009, 05:52 PM
Registered User
 
Join Date: Mar 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Question Hyperlink

I have created below vba code to do exactly what i want but the questions are
  • I want a code that will save my work automatically in a folder and send this as hyperlink to email (Microsft outlook) using below written code
  • Each time i run it, i want it to save as "Stress followed by the current date)

Sub GetRows() ' Copies all rows from sheet 'InputData' with a "Y" in the first column to sheet "Temp1"
Application.ScreenUpdating = False
Count = 1
Sheets("InputData").Select

' Copy Header Row
ABC = "A" & CStr(2) & ":" & "AD" & CStr(2)
ActiveSheet.Range(ABC).Select
Selection.Copy
Sheets("Temp1").Select
ABC = "A" & CStr(Count) & ":" & "AD" & CStr(Count)
Range(ABC).Select
ActiveSheet.Paste
Count = Count + 1
Sheets("InputData").Select

For i = 3 To 10000
If CStr(Cells(i, 1)) = "Y" Or CStr(Cells(i, 1)) = "y" Then
ABC = "A" & CStr(i) & ":" & "AD" & CStr(i)
Range(ABC).Select
Selection.Copy
Sheets("Temp1").Select
ABC = "A" & CStr(Count) & ":" & "AD" & CStr(Count)
Range(ABC).Select
ActiveSheet.Paste
Count = Count + 1
Sheets("InputData").Select

End If
Next i
Calculate

Call CreateEmailBody(Count - 1)

End Sub


Sub CreateEmailBody(NoRows)
Sheets("Temp1").Select
For i = 1 To NoRows
For j = 2 To 30
If i > 1 And j > 1 Then
LineStr = LineStr + " " + CStr(Cells(i, j))
Else
LineStr = LineStr + " " + Cells(i, j)
End If
Next j
'Sheets("Temp2").Select
'Cells(i, 1) = LineStr
'Sheets("Temp1").Select
'LineStr = ""
LineStr = LineStr + "&vbnextline"
Next i
Calculate


End Sub
 
Old March 29th, 2009, 11:33 AM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Quote:
Originally Posted by babajideoluwaseun View Post
I have created below vba code to do exactly what i want but the questions are
  • I want a code that will save my work automatically in a folder and send this as hyperlink to email (Microsft outlook) using below written code
  • Each time i run it, i want it to save as "Stress followed by the current date)
To save your file, check out the SaveAs Method. You can specify a filename which can include the path (folder).

http://msdn.microsoft.com/en-us/library/bb214129.aspx

To send as a hyperlink, see Send links via Outlook email

HTH,
JP

Last edited by JP; March 29th, 2009 at 11:34 AM.. Reason: added signoff





Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink carlos1972 Access VBA 2 November 7th, 2007 10:17 AM
How we can use hyperlink nagham Crystal Reports 0 February 9th, 2007 03:51 AM
hyperlink hyperlink sbhandar Classic ASP Basics 6 August 26th, 2004 02:04 AM
hyperlink pab006 Classic ASP Basics 5 December 17th, 2003 10:33 AM
Hyperlink viktor26 Classic ASP Basics 0 October 19th, 2003 09:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.