By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

Send Attached excel sheet to varied email addresses

P: 34
I am currently trying to create VBA to send a specified excel sheet to varied email sources. I currently have the code below which sends the attachment in an email to a specified source e.g. mrqwe@hotmails.com, however I want to change this so the address is chosen from a specified cell on a worksheet. I have tried to reference a cell e.g. .sendmail Sheets("sheet3").range("A1"),_ but this does not work. Any ideas anyone?

Expand|Select|Wrap|Line Numbers
  1.  Sub Mail_ActiveSheet() 
  2. 'Working in 97-2007
  3. Dim FileExtStr As String
  4. Dim FileFormatNum As Long
  5. Dim Sourcewb As Workbook
  6. Dim Destwb As Workbook
  7. Dim TempFilePath As String
  8. Dim TempFileName As String
  9.  
  10. With Application
  11. .ScreenUpdating = False
  12. .EnableEvents = False
  13. End With
  14.  
  15. Set Sourcewb = ActiveWorkbook
  16.  
  17. 'Copy the sheet to a new workbook
  18. Sheets("Sheet2").Copy
  19. Set Destwb = ActiveWorkbook
  20.  
  21. 'Determine the Excel version and file extension/format
  22. With Destwb
  23. If Val(Application.Version) < 12 Then
  24. 'You use Excel 97-2003
  25. FileExtStr = ".xls": FileFormatNum = -4143
  26. Else
  27. 'You use Excel 2007
  28. 'We exit the sub when your answer is NO in the security dialog that you only
  29. 'see when you copy a sheet from a xlsm file with macro's disabled.
  30. If Sourcewb.Name = .Name Then
  31. With Application
  32. .ScreenUpdating = True
  33. .EnableEvents = True
  34. End With
  35. MsgBox "Your answer is NO in the security dialog"
  36. Exit Sub
  37. Else
  38. Select Case Sourcewb.FileFormat
  39. Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
  40. Case 52:
  41. If .HasVBProject Then
  42. FileExtStr = ".xlsm": FileFormatNum = 52
  43. Else
  44. FileExtStr = ".xlsx": FileFormatNum = 51
  45. End If
  46. Case 56: FileExtStr = ".xls": FileFormatNum = 56
  47. Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
  48. End Select
  49. End If
  50. End If
  51. End With
  52.  
  53. ' 'Change all cells in the worksheet to values if you want
  54. ' With Destwb.Sheets(1).UsedRange
  55. ' .Cells.Copy
  56. ' .Cells.PasteSpecial xlPasteValues
  57. ' .Cells(1).Select
  58. ' End With
  59. ' Application.CutCopyMode = False
  60.  
  61. 'Save the new workbook/Mail it/Delete it
  62. TempFilePath = Environ$("temp") & "\"
  63. TempFileName = "Headcount " & Format(Now, "dd-mmm-yy h-mm-ss")
  64.  
  65. With Destwb
  66. .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
  67. On Error Resume Next
  68. .SendMail "mrqwe@hotmails.com", _
  69. "Headcount&Turnover"
  70. On Error GoTo 0
  71. .Close SaveChanges:=False
  72. End With
  73.  
  74. 'Delete the file you have send
  75. Kill TempFilePath & TempFileName & FileExtStr
  76.  
  77. With Application
  78. .ScreenUpdating = True
  79. .EnableEvents = True
  80. End With
  81. End Sub
  82.  
Feb 7 '08 #1
Share this Question
Share on Google+
12 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Assuming Sheet3 is on the current workbook then try the following to reference the A2 ...

ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1)
Feb 10 '08 #2

P: 34
Assuming Sheet3 is on the current workbook then try the following to reference the A2 ...

ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1)
Unfortunately this did not work but thanks for your reply.
Feb 11 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Unfortunately this did not work but thanks for your reply.
Why didn't it work? The syntax for referencing the value in the cell is correct.
Feb 11 '08 #4

P: 34
Why didn't it work? The syntax for referencing the value in the cell is correct.
I don't know why this does not work I don't get an error messgae but the code does not connect to outlook. below is the VBA I now use with your suggestion:-
.SendMail ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1), _
full code below. Am I doing something wrong?

Expand|Select|Wrap|Line Numbers
  1. Sub Mail_ActiveSheet()
  2. 'Working in 97-2007
  3.     Dim FileExtStr As String
  4.     Dim FileFormatNum As Long
  5.     Dim Sourcewb As Workbook
  6.     Dim Destwb As Workbook
  7.     Dim TempFilePath As String
  8.     Dim TempFileName As String
  9.  
  10.     With Application
  11.         .ScreenUpdating = False
  12.         .EnableEvents = False
  13.     End With
  14.  
  15.     Set Sourcewb = ActiveWorkbook
  16.  
  17.     'Copy the sheet to a new workbook
  18.     Sheets("Sheet2").Copy
  19.     Set Destwb = ActiveWorkbook
  20.  
  21.     'Determine the Excel version and file extension/format
  22.     With Destwb
  23.         If Val(Application.Version) < 12 Then
  24.             'You use Excel 97-2003
  25.             FileExtStr = ".xls": FileFormatNum = -4143
  26.         Else
  27.             'You use Excel 2007
  28.             'We exit the sub when your answer is NO in the security dialog that you only
  29.             'see  when you copy a sheet from a xlsm file with macro's disabled.
  30.             If Sourcewb.Name = .Name Then
  31.                 With Application
  32.                     .ScreenUpdating = True
  33.                     .EnableEvents = True
  34.                 End With
  35.                 MsgBox "Your answer is NO in the security dialog"
  36.                 Exit Sub
  37.             Else
  38.                 Select Case Sourcewb.FileFormat
  39.                 Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
  40.                 Case 52:
  41.                     If .HasVBProject Then
  42.                         FileExtStr = ".xlsm": FileFormatNum = 52
  43.                     Else
  44.                         FileExtStr = ".xlsx": FileFormatNum = 51
  45.                     End If
  46.                 Case 56: FileExtStr = ".xls": FileFormatNum = 56
  47.                 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
  48.                 End Select
  49.             End If
  50.         End If
  51.     End With
  52.  
  53.     '    'Change all cells in the worksheet to values if you want
  54.     '    With Destwb.Sheets(1).UsedRange
  55.     '        .Cells.Copy
  56.     '        .Cells.PasteSpecial xlPasteValues
  57.     '        .Cells(1).Select
  58.     '    End With
  59.     '    Application.CutCopyMode = False
  60.  
  61.     'Save the new workbook/Mail it/Delete it
  62.     TempFilePath = Environ$("temp") & "\"
  63.     TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  64.  
  65.     With Destwb
  66.         .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
  67.         On Error Resume Next
  68.         .SendMail ActiveWorkbook.Worksheets("Sheet3").Cells(2, 1), _
  69.                   "This is the Subject line"
  70.         On Error GoTo 0
  71.         .Close SaveChanges:=False
  72.     End With
  73.  
  74.     'Delete the file you have send
  75.     Kill TempFilePath & TempFileName & FileExtStr
Feb 12 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
If Destwb is the Workbook where this cell is then change the code to ...

.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), "This is the Subject line"
Feb 12 '08 #6

P: 34
If Destwb is the Workbook where this cell is then change the code to ...

.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), "This is the Subject line"
Unfortunately i still can get this to work. Do you know if you can definiently reference a cell with the .Send mail function?
Feb 12 '08 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Unfortunately i still can get this to work. Do you know if you can definiently reference a cell with the .Send mail function?
1. Test what this is returning

Destwb.Worksheets("Sheet3").Cells(2, 1)

2. If it is returning the email address then declare a string variable and pass the value to that first. Then use the variable in the sendmail function.
Feb 12 '08 #8

P: 34
1. Test what this is returning

Destwb.Worksheets("Sheet3").Cells(2, 1)

2. If it is returning the email address then declare a string variable and pass the value to that first. Then use the variable in the sendmail function.
I'm sorry I am not very strong with VBA I am not quite sure what I need to do?
Feb 15 '08 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm sorry I am not very strong with VBA I am not quite sure what I need to do?
Put it in the immediate window and press return.
Feb 15 '08 #10

P: 34
I get the following message:-

compile error:
Expected:=
Feb 18 '08 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
I get the following message:-

compile error:
Expected:=
Try this ...

Destwb.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), _
"This is the Subject line"
Feb 18 '08 #12

P: 34
Try this ...

Destwb.SendMail Destwb.Worksheets("Sheet3").Cells(2, 1), _
"This is the Subject line"
Unfortunately it still does not work. Whenever I try and refer to a cell on the workbook it does not connect to outlook. Strange!

Regards,

Forrest
Feb 19 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.