VBA_Novice_0797
New Member
- Joined
- Jun 7, 2020
- Messages
- 7
- Office Version
- 2016
- Platform
- Windows
Hello,
This is my first post here so apologies in advance if I miss out anything or do not give enough detail!
I have a formatted output (range from worksheet) - that emails itself to me each time new data is added and macro is run. To create this I have adapted code that I have found online from Ron De Bruin to email a range which includes images (some charts and graphs).
The initial code works perfectly, however, the problem I am having is that there is constantly new data being added (this is added in the empty row below latest data) that is not being included in the output as the output is using a fixed range, i.e. ("A1:N10").
Effectively, I need this to offset itself by 1 each time the macro runs, i.e. first time range = ("A1:N10"), second time = ("A1:N11"), third time = ("A1:N12") and so on.
There are two references to the range in the code, initially in the sub and then within the function so I am unsure if both would need to be looped?
I think that the best way to do this is through a loop, however, I am not able to get it to work - please see extract of code with my current attempt below:
I am not sure if I am on the right track or not and would appreciate any help or advice, if you need any more information just let me know!
Windows and Excel 2016.
Thanks,
This is my first post here so apologies in advance if I miss out anything or do not give enough detail!
I have a formatted output (range from worksheet) - that emails itself to me each time new data is added and macro is run. To create this I have adapted code that I have found online from Ron De Bruin to email a range which includes images (some charts and graphs).
The initial code works perfectly, however, the problem I am having is that there is constantly new data being added (this is added in the empty row below latest data) that is not being included in the output as the output is using a fixed range, i.e. ("A1:N10").
Effectively, I need this to offset itself by 1 each time the macro runs, i.e. first time range = ("A1:N10"), second time = ("A1:N11"), third time = ("A1:N12") and so on.
There are two references to the range in the code, initially in the sub and then within the function so I am unsure if both would need to be looped?
I think that the best way to do this is through a loop, however, I am not able to get it to work - please see extract of code with my current attempt below:
VBA Code:
Sub EmailCreation()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim MakeJPG As String
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = ""
MakeJPG = CopyRangeToJPG("Output", "A1:N10")
....
VBA Code:
Function CopyRangeToJPG(Output As String, RangeAddress As String) As String
Dim PictureRange As Range
Dim i As Long
With ActiveWorkbook
On Error Resume Next
.Worksheets("Output").Activate
Set PictureRange = .Worksheets("Output").Range("A1:N10")
For i = 10 To 500
PictureRange.Offset (PictureRange.Rows.Count + 1)
Next i
If PictureRange Is Nothing Then
MsgBox "Picture Range is empty, please check data source"
On Error GoTo 0
Exit Function
End If
....
I am not sure if I am on the right track or not and would appreciate any help or advice, if you need any more information just let me know!
Windows and Excel 2016.
Thanks,