How can I copy different sections of a sheet into a new sheet and send that as a mail.

Nikko · Dec 26, 2018

Hello everyone,

Hope everyone had a merry Christmas and are still in a good mood for the New Year

I put together a code, because I'm not a big light of VBA I have the principle Copy / Paste applied.
Try to insert in my code the desired areas I would like to copy, without much success.

So I ask you for your help with my plans.

How can I insert into my existing VBA code the desired areas I would like to copy and paste into the mail?

Example:
Would like to copy from an existing sheet area B1: C35 and area L1: S35 into a new sheet and send it by mail. Please see my VBA code I would like to insert this process into this code.

Sub Excel_Sheet_via_Outlook_JanEZL()
ActiveWorkbook.ActiveSheet.Unprotect ("s0nne")
Dim GruppenName, KasseMonat As String
GruppenName = ThisWorkbook.Sheets("DPV1").Range("A3")
KasseMonat = Month(CDate(ThisWorkbook.Sheets("DPV1").Range("A2"))) & "/" & Year(CDate(ThisWorkbook.Sheets("DPV1").Range("A2")))
Dim MyMessage As Object, MyOutApp As Object
Dim SavePath As String
Dim AWS As String
SavePath = Environ("TEMP")
Worksheets("DPV1").Copy
' Worksheets("DPV1").Range("B1:C35; L1:S35").Copy
'Range("A3:AJ60").Select
' Selection.Copy
ActiveSheet.UsedRange.Copy
ActiveSheet.Cells().PasteSpecial xlPasteValues
Application.Workbooks(Application.Workbooks.Count).SaveAs ThisWorkbook.Path & "" & "Dienstplanung" & "_" & GruppenName & "_" & Format(Now, "ddmmyyyy__hhmm") & ".xlsx"
With Application.Workbooks(Workbooks.Count)
AWS = .FullName
.Close
End With
Set MyOutApp = CreateObject("Outlook.Application")
Set MyMessage = MyOutApp.CreateItem(0)
With MyMessage
.To = "My.Kollegen@mail.de"
.Subject = "Dienstplanung - Gruppe: " & GruppenName & " - Monat: " & KasseMonat & " - " & Date & "-" & Time
.Attachments.Add AWS
.Body = "Hallo Kollegen," & vbCrLf & vbCrLf & "Im Anhang dieser E-Mail befindet sich deine Dienstplanung in Form einer Excel Datei." & vbCrLf & "Die Datei wird automatisch generiert, bitte beim Aufmachen der Datei alle Vormeldungen zu akzeptieren/aktivieren oder/und auf Weiter zu Drücken." & vbCrLf & vbCrLf & "Zu öffnen mit dem MS Excel Programm oder einem Excel kompatiblen Programm." & vbCrLf & vbCrLf & vbCrLf & "Vielen Dank," & vbCrLf & GruppenName & ""
.GetInspector
.Display
'.Send
Kill AWS
End With
'MyOutApp.Quit
Set MyOutApp = Nothing
Set MyMessage = Nothing
ActiveWorkbook.ActiveSheet.Protect ("s0nne")
End Sub

Thx all in Advance

Nikko

ncrcnbl · Jan 2, 2019

Nikko said:
Hi ncrcnbl,

Many thanks for your effort ,

I inserted it into a module and shows me error / stop at the line

wkbkSource.Worksheets ("DPV1"). Range ("B1: C35, L1: S35"). Copy

Maybe an idea why this may be?

Thx
Nikko

Apologies, "DPV1" was the name of the source worksheet, and should be replaced with whatever the actual source worksheet name is.

How can I copy different sections of a sheet into a new sheet and send that as a mail.

Nikko

Board Regular

ncrcnbl

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

How can I copy different sections of a sheet into a new sheet and send that as a mail.

Nikko

Board Regular

ncrcnbl

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock