LookuUp In VBA

[karldugan]

Hi all,

I have the below code, currently I have hard coded the email to address but I have a list on a "Variables" tab, range J2:K10 (also below) with where the report should go. Rather than updating the code, I was wondering if I could lookup to this list and return the right to person.

Thanks in advance - Karl

SCC	joe.bloggs1@example.com
NIR	joe.bloggs2@example.com
DDR	joe.bloggs3@example.com
LIT	joe.bloggs4@example.com
LXT	joe.bloggs5@example.com
ENF	joe.bloggs6@example.com

'Email report code
Const SCC = "DIRECTORY REMOVED"

'Turn off screen updating
Application.ScreenUpdating = False
'Make a copy of the active sheet and save it to a temporary file
'Selects the SCC sheet
Sheets("SCC").Select
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues

Set wb = ActiveWorkbook
Application.DisplayAlerts = False
wb.SaveAs SCC
Application.DisplayAlerts = True

'Create and show the outlook mail item
Set oMail = CreateObject("Outlook.Application").CreateItem(0)
With oMail 'Add a recipient
.To = "joe.bloggs@example.com"
.Subject = "SCC Report"
.Body = "Please find attached your report."
.Attachments.Add wb.FullName
.send

 

[dave3009]

Hey

Can I just check how this runs overall please? AM I correct in saying that during the first cycle SCC is selected, all the variables in the code snippet refering to SCC (including the to email address) refer to that cycle, then once this is complete it moves onto NIR and everything that was SCC is now NIR...rinse and repeat until ENF?

Thanks
Dave

 

[karldugan]

Hey

Can I just check how this runs overall please? AM I correct in saying that during the first cycle SCC is selected, all the variables in the code snippet refering to SCC (including the to email address) refer to that cycle, then once this is complete it moves onto NIR and everything that was SCC is now NIR...rinse and repeat until ENF?

Thanks
Dave

Hi Dave,

Spot on - code is repeated for each category.

Thanks,
Karl

 

[dave3009]

In this case I would add a third column with the report name, at present you have this as CONST but that seems like you're going to repeat an awful lot.

This code will run through each line, one after the other and generate the report

Sub KarlDugan()
Dim rptRng As Range
Dim rptCode As String
Dim rptTo As String
Dim rptName As String
'add your variables for OMail here

For Each rptRng In Sheet1.Range("J2:J10")

'Email report code, email to and name
rptCode = rptRng.Value 'report code from column J
rptTo = rptRng.Offset(, 1).Value ' email address from column K
rptName = rptRng.Offset(, 2).Value ' report name (formerly CONST) from L

'Turn off screen updating
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Make a copy of the active sheet and save it to a temporary file
'Selects the SCC sheet
Sheets(rptCode).Copy
Set wb = ActiveWorkbook
With wb
    With .Sheets(1).Cells
        .Value = .Value
    End With
    .SaveAs rptName
    rptName = .FullName
End With
Application.DisplayAlerts = True

'Create and show the outlook mail item
Set oMail = CreateObject("Outlook.Application").CreateItem(0)
With oMail 'Add a recipient
    .To = rptTo
    .Subject = rptCode & " Report"
    .Body = "Please find attached your report."
    .Attachments.Add rptName
    .send
End With
End Sub

 

[karldugan]

In this case I would add a third column with the report name, at present you have this as CONST but that seems like you're going to repeat an awful lot.

View attachment 100753

This code will run through each line, one after the other and generate the report

Sub KarlDugan()
Dim rptRng As Range
Dim rptCode As String
Dim rptTo As String
Dim rptName As String
'add your variables for OMail here

For Each rptRng In Sheet1.Range("J2:J10")

'Email report code, email to and name
rptCode = rptRng.Value 'report code from column J
rptTo = rptRng.Offset(, 1).Value ' email address from column K
rptName = rptRng.Offset(, 2).Value ' report name (formerly CONST) from L

'Turn off screen updating
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Make a copy of the active sheet and save it to a temporary file
'Selects the SCC sheet
Sheets(rptCode).Copy
Set wb = ActiveWorkbook
With wb
    With .Sheets(1).Cells
        .Value = .Value
    End With
    .SaveAs rptName
    rptName = .FullName
End With
Application.DisplayAlerts = True

'Create and show the outlook mail item
Set oMail = CreateObject("Outlook.Application").CreateItem(0)
With oMail 'Add a recipient
    .To = rptTo
    .Subject = rptCode & " Report"
    .Body = "Please find attached your report."
    .Attachments.Add rptName
    .send
End With
End Sub

Hi Dave,

Thanks for the above & help so far, when I run, I am getting a compile error that says "For without Next"

Karl

 

[dave3009]

Change the end two lines to these three

End With
Next rptRng
End Sub