KlausW
Active Member
- Joined
- Sep 9, 2020
- Messages
- 458
- Office Version
- 2016
- Platform
- Windows
Hi, I have a little challenge. I use this VBA code to save the 3sheets and mail them. It works really well. Now I want to add a little change but cannot figure out how to put it in.
The change shout be, that if I make a X in cell K1 in the sheet “Fast besætning” the VBA code shall only save the fil and nor send it by mail.
Some that can help get it running.
All help will be appreciated.
Best Regards
Klaus W
The change shout be, that if I make a X in cell K1 in the sheet “Fast besætning” the VBA code shall only save the fil and nor send it by mail.
Some that can help get it running.
All help will be appreciated.
Best Regards
Klaus W
VBA Code:
Sub Rektangelafrundedehjørner5_Klik()
Dim Fname As String, ws As Worksheet, wbk As Workbook
Set wbk = ThisWorkbook
Fname = Sheets("Fast besætning").Range("g1").Value
Sheets(Array("1.deling", "2.deling", "3.deling")).Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Value = .Value
End With
Next ws
With ActiveWorkbook
Application.Dialogs(xlDialogSaveAs).Show Fname, 51
End With
With wbk
.Activate
.Sheets("Fast besætning").Select
End With
Mail_workbook_Outlook
End Sub
Sub Mail_workbook_Outlook()
'her er koden til at sende mail
Dim Edress As String, Subj As String
Dim OutlookOBJ As Object, mItem As Object
'---------------------------------------------'
Set OutlookOBJ = CreateObject("Outlook.Application")
Set mItem = OutlookOBJ.CreateItem(0)
On Error Resume Next
With mItem
.To = Sheets("Fast besætning").Range("g2").Value & "; " & Range("g3").Value & "; " & Range("g4").Value & "; " & Range("g5").Value & "; " & Range("g6").Value & "; " & Range("g7").Value & "; " & Range("g8").Value & "; " & Range("g9").Value & "; " & Range("g10").Value & "; " & Range("g11").Value & "; " & Range("g12").Value & "; " & Range("g13").Value & "; " & Range("g14").Value & "; " & Range("g15").Value & "; " & Range("g16").Value & "; " & Range("g17").Value & "; " & Range("g18").Value & "; " & Range("g19").Value & "; " & Range("g20").Value & "; " & Range("g21").Value & "; " & Range("g22").Value
.CC = ""
.BCC = ""
.Subject = Sheets("Fast besætning").Range("f1").Value
.Body = Range("l1").Value & vbNewLine & vbNewLine & Range("l2").Value & vbNewLine & _
Range("l3").Value & vbNewLine & Range("l4").Value & vbNewLine & Range("l5").Value
'& vbNewLine & _ Range("n8").Value
ThisWorkbook.Save
.Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
.Display
'.Send '<-- .Send will auto send email without review
End With
End Sub