Imam jednu excel aplikaciju (to je izvestaj) koji se svakodnevno dopunjuje, po zavrsetku se izmene snime i taj deo savrseno radi, tu nemam pitanja. Medjutim ostaju sledeci problemi, po zavrsetku izvestaja izvestaj zelim:
- snimiti na drugoj particiji (ali samo value i da se zadrzi izgled - formating, bez formula)
- dokument treba poslati meilom ( ali zelim poslati samo value i format, bez formula)
Trazio sam po internetu, kupio sam i jednu veoma prakticnu knjigu u vezi toga, ali mi sve to ne resava problem. Ja cu zakaciti fajlove koji delimicno resavaju problem, kao i sam code, pa da probamo da nadjemo resenje, verujem da se cesto ovakva potreba moze javiti i drugim diskutantima.
Ovo je macro koji pravi rezervnu kopiju dokumenta sa dodavanjem datuma. Gde u ovom kodu dodati lokaciju i gde treba dodati da kopirani fajl sadrzi samo value vrednosti i da se zadrzi izgled (format)
Code:
Sub Macro15()
'Step 1: Save workbook with new file name
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & _
Format(Date, "MM-DD-YY") & " " & _
ThisWorkbook.Name
End Sub
Sub Macro15()
'Step 1: Save workbook with new file name
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & _
Format(Date, "MM-DD-YY") & " " & _
ThisWorkbook.Name
End Sub
Sledeci makro je za slanje aktivne knjige (workbook) kao attachment
Code:
Sub Macro85()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 3: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
.Display 'Change to .Send to send without reviewing
End With
'Step 4: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Sub Macro85()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 3: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
.Display 'Change to .Send to send without reviewing
End With
'Step 4: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Opet imam problem sto salje kompletnu knjigu sa formulama, a meni treba samo value vrednosti
Sledeci makro salje pojedinacnu stranu iz knjige
Code:
Sub Macro87()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Copy Worksheet, paste to new workbook, and save it
Sheets("Revenue Table").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "New Workbook Attached"
.Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx")
.Display 'Change to .Send to send without reviewing
End With
'Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 6: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Sub Macro87()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Copy Worksheet, paste to new workbook, and save it
Sheets("Revenue Table").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "New Workbook Attached"
.Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx")
.Display 'Change to .Send to send without reviewing
End With
'Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 6: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Ali ni ovo ne vrsi posao zato sto u knjizi (izvestaju ima 4-5 strana (sheets)
Poslednji makro salje odredjeno podrucje iz dokumenta (knjige) kao attachment
Code:
Sub Macro86()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Copy range, paste to new workbook, and save it
Sheets("Revenue Table").Range("A1:E7").Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteValues
Range("A1").PasteSpecial xlPasteFormats
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx")
.Display 'Change to .Send to send without reviewing
End With
'Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 6: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Sub Macro86()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Copy range, paste to new workbook, and save it
Sheets("Revenue Table").Range("A1:E7").Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteValues
Range("A1").PasteSpecial xlPasteFormats
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add (ThisWorkbook.Path & "\TempRangeForEmail.xlsx")
.Display 'Change to .Send to send without reviewing
End With
'Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & "\TempRangeForEmail.xlsx"
'Step 6: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
U ovom makrou ima navedeno da ono sto se salje bude values i da bude formatirano, ali je problem sto makro salje samo odredjeni deo knjige.
Kako da se iskombinuje da se naprave dva odvojena makroa koji ce snimiti rezervnu kopiju i drugi koji ce poslati kopiju meilom, ali da to u oba slucaja bude samo value bez formula.