Hi Everyone,
I'm very new to vba and I have been searching for a little while now to find out the following:
1) How to prompt a user to enter the number of rows that contain data (entered as a number) (e.g. answer might be 3.)
2) I then want to prompt the user for the starting Row number for the data (e.g 4). The Column will always be the same, however the number of rows and the starting row number will vary.
3) How to get outlook to put in a from address (we have group email accounts and I need to send the e-mail on behalf of the group).
For an example - the end result will be that row 4-7 contain the data.
So far i have had no luck in finding an answer....
The data will be used later in the macro to build a custom e-mail message. I've only just started on this, so there isn't much code at the moment.
Thanks for all the help: - this site is great!
The code so far - please bear in mind that it is messy and has lots of comments for things still to be done as I get to them....
If anyone can come up with neater code - please do.
This is run from an internal network with multiple users and outlook/office 2002/2003 enviroments....
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
Dim fso As Object, ts As Object
Set fso = CreateObject("Scripting.FileSystemObject"),
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
Dim OutApp As Object, OutMail As Object
Dim strbody As String, SigString As String, Signature As String
Sub mailtest()
'Need to reference: Microsoft Forms 2.0 Object Library
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
' Compose the message
strbody1 = "<FONT size=4 face=TimesNewRoman><P><u><b><DIV Align=center>Possible Vulnerabilitys</b></u></div><P>"
strbody2 = "<font size=3>This vulnerability has been identified by <FONT color=red>" & Cells(29, 1) & "</font><p><p>"
'Cells to bring up company that vulnerability e.g. Microsoft
' Need to prompt for number of rows to include (e.g one vulnerability notification may contain a number of vulnerabilities - hence more rows?? (maybe use columns...)
' Executive/Broad Overview of Vulnerability as required - to be entered via cell references hopefully...
strbody3 = "This is a general description <p> "
strbody4 = "The following URLS cover this vulnerability: <br>"
strbody5 = "1) " & Cells(29, 3) & "<br>"
strbody6 = "2) " & Cells(29, 4) & "<br>"
strbody7 = "3) " & Cells(29, 5) & "<p>"
strbody8 = "Affected Software: <font color=red>" & Cells(29, 6) & "</font>"
strbody9 = "An initial risk assessment of <font color=red>" & Cells(29, 7) & "</font> for the XXX and <font color=red>" & Cells(29, 7) & "</font> for the XXX has been given to this Vulnerability. "
strbody10 = "<p>Patches are available at: <br> 1)" & Cells(29, 4)
strbody11 = "<p>Workaround: " & Cells(29, 5)
strbody12 = "Staff are requested to evaluate the level of exposure for your respective areas of responsibility and add a brief entry into XXXXXX (<font color=red>" & Cells(2, 1) & "</font>) and XXX (<font color=red>" & Cells(2, 3) & ")</font> to show your findings and indicate test plan and timelines. Please also e-mail your findings to 'Reply to All'. <br> If you are unable to access XXXXX, send us the data and we would be happy to enter it for you."
'strbody13 =
'attachmnt = "N:\FolderOne\Proofs\" & prooffilename & "\" & prooffilename & ".pdf"
'Get Signature
SigString = "H:\profile\Application Data\Microsoft\Signatures\Mysig.htm"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
'Create E-mail and display with above information
With OutMail
.to = "pjce@test.com"
.cc = "test@test.com"
.Subject = "Possible Vulnerabilities in " & Cells(1, 1)
.HTMLBody = strbody1 & strbody2 & strbody3 & strbody4 & strbody5 & strbody6 & strbody7 & strbody9 & strbody10 & strbody11 & strbody12 & strbody13 & Signature
'.Attachments.Add (attachmnt)
.Display
.Save
End With
'now send! this tricks it into thinking you manually hit send. Basically disables warnings.
'SendKeys "%{s}", True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
I'm very new to vba and I have been searching for a little while now to find out the following:
1) How to prompt a user to enter the number of rows that contain data (entered as a number) (e.g. answer might be 3.)
2) I then want to prompt the user for the starting Row number for the data (e.g 4). The Column will always be the same, however the number of rows and the starting row number will vary.
3) How to get outlook to put in a from address (we have group email accounts and I need to send the e-mail on behalf of the group).
For an example - the end result will be that row 4-7 contain the data.
So far i have had no luck in finding an answer....
The data will be used later in the macro to build a custom e-mail message. I've only just started on this, so there isn't much code at the moment.
Thanks for all the help: - this site is great!
The code so far - please bear in mind that it is messy and has lots of comments for things still to be done as I get to them....
If anyone can come up with neater code - please do.
This is run from an internal network with multiple users and outlook/office 2002/2003 enviroments....
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
Dim fso As Object, ts As Object
Set fso = CreateObject("Scripting.FileSystemObject"),
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
Dim OutApp As Object, OutMail As Object
Dim strbody As String, SigString As String, Signature As String
Sub mailtest()
'Need to reference: Microsoft Forms 2.0 Object Library
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
' Compose the message
strbody1 = "<FONT size=4 face=TimesNewRoman><P><u><b><DIV Align=center>Possible Vulnerabilitys</b></u></div><P>"
strbody2 = "<font size=3>This vulnerability has been identified by <FONT color=red>" & Cells(29, 1) & "</font><p><p>"
'Cells to bring up company that vulnerability e.g. Microsoft
' Need to prompt for number of rows to include (e.g one vulnerability notification may contain a number of vulnerabilities - hence more rows?? (maybe use columns...)
' Executive/Broad Overview of Vulnerability as required - to be entered via cell references hopefully...
strbody3 = "This is a general description <p> "
strbody4 = "The following URLS cover this vulnerability: <br>"
strbody5 = "1) " & Cells(29, 3) & "<br>"
strbody6 = "2) " & Cells(29, 4) & "<br>"
strbody7 = "3) " & Cells(29, 5) & "<p>"
strbody8 = "Affected Software: <font color=red>" & Cells(29, 6) & "</font>"
strbody9 = "An initial risk assessment of <font color=red>" & Cells(29, 7) & "</font> for the XXX and <font color=red>" & Cells(29, 7) & "</font> for the XXX has been given to this Vulnerability. "
strbody10 = "<p>Patches are available at: <br> 1)" & Cells(29, 4)
strbody11 = "<p>Workaround: " & Cells(29, 5)
strbody12 = "Staff are requested to evaluate the level of exposure for your respective areas of responsibility and add a brief entry into XXXXXX (<font color=red>" & Cells(2, 1) & "</font>) and XXX (<font color=red>" & Cells(2, 3) & ")</font> to show your findings and indicate test plan and timelines. Please also e-mail your findings to 'Reply to All'. <br> If you are unable to access XXXXX, send us the data and we would be happy to enter it for you."
'strbody13 =
'attachmnt = "N:\FolderOne\Proofs\" & prooffilename & "\" & prooffilename & ".pdf"
'Get Signature
SigString = "H:\profile\Application Data\Microsoft\Signatures\Mysig.htm"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
'Create E-mail and display with above information
With OutMail
.to = "pjce@test.com"
.cc = "test@test.com"
.Subject = "Possible Vulnerabilities in " & Cells(1, 1)
.HTMLBody = strbody1 & strbody2 & strbody3 & strbody4 & strbody5 & strbody6 & strbody7 & strbody9 & strbody10 & strbody11 & strbody12 & strbody13 & Signature
'.Attachments.Add (attachmnt)
.Display
.Save
End With
'now send! this tricks it into thinking you manually hit send. Basically disables warnings.
'SendKeys "%{s}", True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
